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

ID: Q167606

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

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:

Run the procedure.

Perform sp_recompile on an underlying permanent table referenced in the procedure.

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:

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

Keywords : kbusage SSrvStProc kbbug6.50 kbbug6.00 kbfix6.50.sp2
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbbug