Microsoft KB Archive/172571

= FIX: Access Violation When Querying a View with a CASE WHEN EXISTS Clause and FORCEPLAN Is ON =

Article ID: 172571

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q172571



BUG #: 17128 (SQLBUG_65)



SYMPTOMS
When querying a view with a CASE WHEN EXISTS clause and FORCEPLAN is ON, an access violation (AV) may occur. The following sample scripts demonstrate this problem: DROP TABLE t GO

SELECT c = 1

INTO t

UNION SELECT c = 2

DROP VIEW vT GO

CREATE VIEW vT AS

SELECT CASE WHEN EXISTS (SELECT * FROM t t1 WHERE t1.c = t2.c) THEN 1 ELSE 0 END AS col FROM t t2

GO

SET FORCEPLAN ON GO

SELECT * FROM vT GO You might see the following error messages in the SQL Server error log:

EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump

Initializing symptom dump and stack dump facilities


 * BEGIN STACK TRACE***

0x0042F2C8 in SQLSERVR.EXE, newlinklock + 0x0418

0x00433717 in SQLSERVR.EXE, opendb + 0x00F7

0x0042ED45 in SQLSERVR.EXE, check_deadlock + 0x0675

0x0042EFA8 in SQLSERVR.EXE, newlinklock + 0x00F8

0x0051B4DE in SQLSERVR.EXE, find_eop_subst + 0x00CE

0x004B3493 in SQLSERVR.EXE, MSSqlSDINewSP + 0x0173

0x0051A905 in SQLSERVR.EXE, prEOP + 0x0165

0x004B2D9C in SQLSERVR.EXE, MSSqlSDIGetVars + 0x023C

0x0042ED45 in SQLSERVR.EXE, check_deadlock + 0x0675

0x0042EFA8 in SQLSERVR.EXE, newlinklock + 0x00F8

0x00423B50 in SQLSERVR.EXE, dbswriteflush + 0x0160

0x004229BD in SQLSERVR.EXE, dbswritecheck + 0x07AD

0x0040E680 in SQLSERVR.EXE, ksconsole + 0x0320

0x0040F1E5 in SQLSERVR.EXE, initcfgfix + 0x03D5

0x0040ED45 in SQLSERVR.EXE, initconfig + 0x0165

0x0040B7B2 in SQLSERVR.EXE, SqlDumpLocks + 0x0052

0x00415217 in SQLSERVR.EXE, udasyncwrite + 0x0187

0x00250FED in opends60.dll

0x0025055B in opends60.dll

0x002414D1 in opends60.dll

0x00241384 in opends60.dll

0x10219D84 in MSVCRT40.dll

0x77F04F32 in KERNEL32.dll


 * END STACK TRACE***

On the client side, the application will receive the following error:

DB-Library Process Dead - Connection Broken



WORKAROUND
To work around this problem, do one of the following:  Issue a SET FORCEPLAN OFF command. Doing this avoids the problem.  Create a temporary table instead of using a view. The following scripts demonstrate the workaround for the above scenario: DROP TABLE t GO

SELECT c = 1 INTO t UNION SELECT c = 2

SELECT CASE WHEN EXISTS (SELECT * FROM t t1 WHERE t1.c = t2.c) THEN 1 ELSE 0 END AS col INTO #t FROM t t2

SET FORCEPLAN ON GO

SELECT * FROM #T GO 



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Additional query words: enabled disable

Keywords: kbbug kbfix kbusage KB172571

-

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

© Microsoft Corporation. All rights reserved.