Microsoft KB Archive/138761

= INF: Differences in Column Nullability =

Article ID: 138761

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft Open Database Connectivity 2.5

-



This article was previously published under Q138761



SUMMARY
The nullability of columns in SQL Server temporary tables referenced by stored procedures can affect the operation of stored procedures.



MORE INFORMATION
The execution plans built for SQL Server stored procedures contain information on the nullability (the property of allowing or not allowing nulls) of all the table columns referenced by the stored procedure. Proper execution of the stored procedure depends on the nullability information stored in the execution plan agreeing with the actual nullability of the tables being referenced.

If sites are running a mix of DB-Lib and ODBC clients executing stored procedures on SQL Server 6.0 or later, it is important that all temporary tables referenced by those stored procedures be created with NULL or NOT NULL explicitly stated for all of the columns. This is because ODBC and DB- Lib clients have different defaults regarding whether columns are NULL or NOT NULL if this is not explicitly stated.

Prior to version 6.0, SQL Server followed non-ANSI rules regarding nullability defaults. SQL Server 6.0 introduced the command:

SET ANSI_NULL_DFLT_ON ON

which clients can use to change their default to the ANSI standard. Since the ODBC standard is based on the ANSI standard, a 2.50.0121 or later Microsoft SQL Server ODBC Driver sets this option on when it detects it is running against a 6.0 or later SQL Server. DB-Lib clients, however, are more targeted to maintaining compatibility with older SQL Servers, and they do not set this option. If a procedure like the following is created through a DB-Lib application, such as ISQL/w or the SQL Enterprise Manager:

create procedure testproc as  create table #testit (cola int, colb char(3)) insert into #testit values (1, 'aaa') select * from #testit go

then the temporary table #testit will be created with the columns not allowing nulls. This fact will be recorded in the stored procedure. When the procedure is later executed by an ODBC client, however, the temporary table created for the ODBC client will allow nulls in both columns. This conflict between what is recorded in the stored procedure and the current version of the table can prevent the proper operation of the stored procedure. All problems will be prevented if the procedure is changed to explicitly state NULL or NOT NULL for each column:

create procedure testproc as  create table #testit (cola int NOT NULL, colb char(3) NOT NULL) insert into #testit values (1, 'aaa') select * from #testit go

The same type of problems could also occur if the first procedure were created from an ODBC application and later executed by a DB-Lib application.

An example of the types of problems encountered is inherent in the fact that the nullability of the columns has been changed. If a procedure were created and tested with ODBC Test, the procedure would allow nulls to be added to the temporary table simply because the columns default to NULL. If the procedure were then executed from ISQL/w it would start generating errors if the user attempted to insert null data because the version of the temporary table created for the DB-Lib client would default to NOT NULL.

If a stored procedure references a temporary table that is created outside the procedure, this issue can also affect any version of the Microsoft ODBC driver running against SQL Server 4.21a or earlier servers. This is because if NULL or NOT NULL is not specified on any CREATE TABLE statement issued through the drivers, the drivers automatically add NULL to the column definition before passing the statement to the server. For example, if the following procedure is created in a 4.21a server using ISQL/w:

create table #testit (cola int, colb char(3)) go  create procedure testproc as   insert into #testit values (1, 'aaa') select * from #testit go

and then an ODBC application does:

SQLExecDirect(hstmt, "create table #testit                        (cola int, colb char(3))", SQL_NTS); SQLExecDirect(hstmt, "execute testproc", SQL_NTS);

Microsoft ODBC drivers will change the create table statement to:

create table #testit (cola int null, colb char(3) null)

before sending it to the server in order to enforce the ANSI standard.

Additional query words: sql6 dblib odbc

Keywords: kbinterop kbprogramming kbusage KB138761

-

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

© Microsoft Corporation. All rights reserved.