BUG: If ANSI_DEFAULTS On, Join Sequences in St. Procs Cause AV

• Microsoft SQL Server version 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 version 6.5. A supported fix is now available, but has not been fully regression- tested and should be applied only to systems experiencing this specific problem. Unless you are severely impacted by this specific problem, Microsoft recommends that you wait for the next Service Pack that contains this fix. Contact Microsoft Technical Support for more information.

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

  -and-

• SET ANSI_PADDING ON