Microsoft KB Archive/308818

= FIX: SP1 Regression: SELECT Query on a Table with a Computed Column Generates Access Violation =

Article ID: 308818

Article Last Modified on 1/10/2002

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 1

-



This article was previously published under Q308818



BUG #: 354576 (SHILOH_BUGS)



SYMPTOMS
If you execute a SELECT statement with a nested query on a table that has a computed column, the following Access Violation may occur:

ODBC: Msg 0, Level 19, State 1

SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.

SQL Server is terminating this process.

Connection Broken

The following stack dump appears in the SQL Server error log: -- Short Stack Dump

004752B8 Module(sqlservr+000752B8) (CScaOp_Identifier::CScaOp_Identifier+0000000B)

00745D5D Module(sqlservr+00345D5D) (CScaOp_Identifier::PopRemap+0000006F)

006F8FB2 Module(sqlservr+002F8FB2) (COptExpr::PexprRemap+0000008E)

006F8F81 Module(sqlservr+002F8F81) (COptExpr::PexprRemap+0000005C)

00728137 Module(sqlservr+00328137) (CTableMetadata::FLoadFromCache+0000021E)

0044FC75 Module(sqlservr+0004FC75) (CTableMetadata::LoadMetadata+0000002B)

00461EA5 Module(sqlservr+00061EA5) (CTableMetadataCache::LoadMetadata+00000030)

0044FCE4 Module(sqlservr+0004FCE4) (CTableMetadata::LoadMetadata+000001C2)

00478A64 Module(sqlservr+00078A64) (CLogOp_Get::DeriveGroupProperties+00000022)

0044FB2F Module(sqlservr+0004FB2F) (COpArg::DeriveNormalizedGroupProperties+0000001B)

0044FABB Module(sqlservr+0004FABB) (COptExpr::DeriveGroupProperties+000000B3)

0044FA65 Module(sqlservr+0004FA65) (COptExpr::DeriveGroupProperties+0000005D)

0044FA65 Module(sqlservr+0004FA65) (COptExpr::DeriveGroupProperties+0000005D)

0045F28E Module(sqlservr+0005F28E) (CCvtTree::PexprFromTree+0000032D)

0045EF4E Module(sqlservr+0005EF4E) (CCvtTree::PqryFromTree+0000024C)

0045ED19 Module(sqlservr+0005ED19) (BuildQueryFromTree+00000046)

0045EC86 Module(sqlservr+0005EC86) (CStmtQuery::InitQuery+0000013E)

0047979A Module(sqlservr+0007979A) (CStmtSelect::Init+0000008A)

00444A76 Module(sqlservr+00044A76) (CCompPlan::FCompileStep+00000AD6)

00446B6F Module(sqlservr+00046B6F) (CProchdr::FCompile+00000CFA)

00412896 Module(sqlservr+00012896) (CSQLSource::FTransform+0000034C)

0044B995 Module(sqlservr+0004B995) (CSQLStrings::FTransform+000001A1)

0041227B Module(sqlservr+0001227B) (CSQLSource::Execute+0000015B)

0044C1DD Module(sqlservr+0004C1DD) (language_exec+000003E1)

00411DA0 Module(sqlservr+00011DA0) (process_commands+000000E0)

41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)

41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)

7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)

77E8758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A) --- For example, use this code to create a table with a computed column: USE PUBS go CREATE TABLE [dbo].[table1] (   [col1] [varchar] (10)  NOT NULL,    [col2] AS (([col3]*10)) ,    [col3] [int] NOT NULL ) ON [PRIMARY] GO The following query may generate the Access Violation: SELECT * FROM dbo.table1 WHERE col1 = (SELECT 1 from dbo.table1)



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, first place the value of the computed column into a variable, and then use that variable in the SELECT list: DECLARE @variable int SET @variable= (SELECT 1 from dbo.table1)

SELECT * FROM dbo.table1 WHERE col1 = @variable Selecting the columns explicitly and adding a computation in the query to replace the computed column also avoids the problem. CREATE TABLE [dbo].[table1] (   [col1] [varchar] (10)  NOT NULL,    [col3] [int] NOT NULL ) ON [PRIMARY] GO

SELECT col1, (col3*10) as col2, col3 FROM dbo.table1 WHERE col1 = (SELECT 1 from dbo.table1)



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000 Service Pack 1. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

Additional query words: AV checksum sp1 computed column

Keywords: kbbug kbfix KB308818

-

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

© Microsoft Corporation. All rights reserved.