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:
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.