Microsoft KB Archive/167606

= FIX: Err 511: Stored Proc w/ANSI_NULL_DFLT_ON Creates Temp Tbl =

Article ID: 167606

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q167606



BUG #: 16814 (NT: 6.5)



SYMPTOMS
A stored procedure that creates a temporary table without explicitly specifying column nullability and INSERTS into that table will fail with an error 511 if the procedure was created with ANSI_NULL_DFLT_ON set on, and is run with ANSI_NULL_DFLT_ON set off. This problem occurs if you do all of the following:


 * 1) Run the procedure.
 * 2) Perform sp_recompile on an underlying permanent table referenced in the procedure.
 * 3) Run the procedure again.

The error 511 occurs on the second execution after the recompile. All ODBC connections set ANSI_DEFAULTS on, which in turn sets ANSI_NULL_DFLT_ON on. Therefore, any procedure created over ODBC is susceptible to this problem.



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


 * Explicitly specify columns as NULL, or NOT NULL during the table definition in the stored procedure.

-or-
 * Specify the ANSI_NULL_DFLT_ON setting for clients before running the stored procedure.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.



MORE INFORMATION
A similar problem occurs if the tables created in the procedure are permanent tables. For more information, see the following article in the Microsoft Knowledge Base:

167605 : BUG: Err 511: Stored Proc w/ANSI_NULL_DFLT_ON Creates Perm Tbl

Keywords: kbbug kbfix kbusage KB167606

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.