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

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.