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

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:

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