FIX: AV in Query with a Subselect on a View with a JOIN and FORCEPLAN Is On

ID: Q176493


The information in this article applies to:

BUG #: 17418 (SQLBUG_65)

SYMPTOMS

A handled access violation (AV) may occur if all of the following conditions are true:

If all of these conditions are true, a DB-Library client may receive the following error:

DB-Library Process Dead - Connection Broken


An ODBC client may receive the following error:

[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream


You may see in the SQL Server error log messages similar to the following:


   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
   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 


WORKAROUND

To work around this problem, do either of the following:


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.


MORE INFORMATION

Steps to Reproduce the Problem

  1. Use the following to create the view:
    
    use pubs
    go
    create view v1 AS
    select ta.title_id, t.pub_id
    from titleauthor ta
    inner join titles t on t.title_id = ta.title_id 


  2. Use the following query to cause an access violation:
    
    set forceplan on
    select distinct * from v1
    where pub_id in (select pub_id from publishers)
    set forceplan off 




The following query demonstrates the workaround:

set forceplan on
select distinct v1.* from v1
inner join publishers p on v1.pub_id=p.pub_id
set forceplan off 

Additional query words: joinorder join order exception


Keywords          : kbusage SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 21, 1999