Knowledge Base

FIX: If ANSI_DEFAULTS On, Join Sequences in Stored Procedures Cause an Access Violation

Article ID: 175142

Article Last Modified on 3/14/2006

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q175142

BUG #: 17149 (6.5)

SYMPTOMS

If ANSI_DEFAULTS are on, and you repeatedly perform CREATE TABLE, INSERT joins, and UPDATE joins from within a stored procedure, an ANSI warning may be generated. This can cause a variety of access violation (AV) errors, and the server may stop responding (hang). The problem can occur on all versions of SQL Server from build 6.50.201 through build 6.50.266. The problem does not occur on SQL Server version 6.0, because the ANSI_DEFAULTS setting does not exist in that version.

The Microsoft ODBC SQL Server driver version 2.65 sets ANSI_DEFAULTS on by default when running with SQL Server 6.5. Therefore, this behavior may be more likely to occur with an ODBC application than with a DB-Library application.

The problem conditions do not require multiple concurrent query instances, and can happen during a single query stream.

Errors received include error numbers 510, 605, 614, 2620, and 2624. The frequency of occurrence varies. The following are some error messages that you may receive:

Error: get_spinlock:Spinlock type 7 address (0x188e676) isn't aligned getspinlock: spid 11, 1 spins, 1000 yields on lock type 7(a 0x188e676)

writelog: timeout, dbid 2, dbstat2 0x22(0x22), q1/q2 1/1, owner=12, waittype=0

getindex: rowptr (0x4854554f) outside page range 0x1120800 - 0x34

WORKAROUND

You can use any of several methods to work around the problem, including the following:

Locate the ANSI violation in your query and alter the query to avoid it.
Programmatically disable ANSI_DEFAULTS, ANSI_WARNINGS, or ANSI_PADDING from within the ODBC application.
Using the ODBC Administrator utility in Control Panel, click to clear the "Use ANSI Nulls, Padding and Warnings" check box.
Make a slight change to the query so that its syntax is different, but still produces the same result set. Testing indicates that making very slight changes may avoid the problem.

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.

MORE INFORMATION

Setting ANSI_DEFAULTS on enables several individual behaviors. The following specific combination of ANSI settings is necessary for the problem to occur:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON

A query that produces an ANSI warning is a prerequisite for this problem to occur, although most queries that produce an ANSI warning do not cause it. For more information on ANSI warnings, see the online documentation.

Additional query words: hang hung sproc stproc proc st dblib db-lib

Keywords: kbbug kbfix kbenv kbusage KB175142

Microsoft KB Archive/175142

Contents