FIX: If ANSI_DEFAULTS On, Join Sequences in Stored Procedures Cause an Access Violation
ID: Q175142
|
The information in this article applies to:
-
Microsoft SQL Server version 6.5
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
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
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 : kbenv kbusage SSrvGen SSrvStProc kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug