Microsoft KB Archive/272418

= BUG: Access Violation Occurs with Non-Updateable Derived Table in UPDATE Statement =

Article ID: 272418

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q272418



BUG #: 236027 (SHILOH)



SYMPTOMS
If you run a query that tries to update a derived table that contains a UNION statement, a handled Access Violation occurs and the connection is closed. For example, the following query: declare @x int update t set @x = coalesce( @x, 0 ) + n from ( select 1 union all select 2 union all select 3 ) as t( n ) returns this error message:

ODBC: Msg 0, Level 19, State 1

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005

EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Connection Broken

SQL Server also prints a stack dump to the error log with text similar to the following example. Please check the error log for the Access Violation and note that the Exception Address is in (res_view(class CAlgStmt *,class TREE * *): 2000-08-28 12:13:37.77 spid51   Error: 0, Severity: 19, State: 0 2000-08-28 12:13:37.77 spid51    SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
 * BEGIN STACK DUMP:
 * 08/28/00 12:13:37 spid 51
 * Exception Address = 0067CA0C (res_view(class CAlgStmt *,class TREE * *) + 000003E3 Line 0+00000000)
 * Exception Code   = c0000005 EXCEPTION_ACCESS_VIOLATION
 * Access Violation occurred reading address 00000000
 * Input Buffer 256 bytes -
 * declare @x int update t  set @x = coalesce( @x, 0 ) + n  from ( select
 * 1 union all select 2 union all select 3 ) as t( n )
 * Input Buffer 256 bytes -
 * declare @x int update t  set @x = coalesce( @x, 0 ) + n  from ( select
 * 1 union all select 2 union all select 3 ) as t( n )



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
A derived table cannot be updated if the definition of the table contains a UNION operator. The Transact-SQL statement is invalid and must be corrected.

Microsoft SQL Server 7.0 returns the correct error message for the statement. For example:

Server: Msg 4417, Level 16, State 1, Line 2

Derived table 't' is not updatable because the definition contains a

UNION operator.

Keywords: kbbug kbcodesnippet kbpending KB272418

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.