FIX: Fail to Pass NULLs as Char/Varchar When Invoking a SProc

• Microsoft SQL Server, version 6.0

SYMPTOMS

When a stored procedure is invoked with ODBC procedure syntax, passing NULLs as char or varchar parameters into this stored procedure will raise the following error:

   szSqlState = "22005", *pfNativeError = 257, *pcbErrorMsg = 160
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server] Implicit
   conversion from datatype 'money' to 'char' is not allowed.  Use the
   CONVERT function to run this query."

WORKAROUND

There are two ways to avoid this problem:

1. Use the EXECUTE keyword to invoke stored procedures. Using this method, a stored procedure is treated as a language event rather than a Remote Procedure Call, and the application will suffer some performance loss.

2. Replace NULLs with two single quotation marks (''). This is a temporary workaround only for this version of the SQL Server Driver.

STATUS

Microsoft has confirmed this to be a problem in SQL Server Driver version 2.50.0121. This problem has been corrected in SQL Server 6.0 Service Pack 2. For more information, contact your primary support provider.

MORE INFORMATION

Here are the steps to reproduce the error using ODBC Test:

1. SQLExecuteDirect: create table test(col1 char(10), col2 varchar(10))

2. SQLExecuteDirect: create proc stest @p1 char(10), @p2 varchar(10) as insert into test values(@p1, @p2)

3. SQLExecuteDirect: {call stest(NULL, NULL)}