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)}

Additional query words: ODBC NULL RPC EXECUTE

Keywords          : kbprg SSrvProg SSrvStProc kbbug2.50.0121 kbbug6.00 kbfix6.00.sp1 
Version           : 2.5.0121 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 19, 1999