BUG: Msg 257 When Inserting NULL values by RPC (172335)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q172335
BUG #: 17124 (SQL 6.5)

SYMPTOMS

If you use an INSERT statement with a remote stored procedure call to insert NULL values, you may receive the following error:
Msg 257
Implicit conversion from datatype 'int' to 'varchar' is not allowed. Use
the CONVERT function to run this query.

The same script running on a local server works correctly.

This problem has been tested on SQL Server build 6.50.258.

WORKAROUND

To work around this problem, replace NULL values with '' (null string).

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server 6.5.

MORE INFORMATION

The following script demonstrates the problem: 
   On server A
   -----------
				
   create table rpctestA
   (
   col1 char(2) not null,
   col2 char(2) null
   )

   create proc insert_rpctestA
   @sNo char(2)
   as
   begin
      insert rpctestA
      exec <server B>.pubs.dbo.select_rpctestB @sNo
   end
				

   On server B
   -----------
				
   create table rpctestB
   (
   col1 char(2) not null,
   col2 char(2) null
   )

   insert rpctestB values ('AA', 'BB')

   create proc select_rpctestB
   @sNo char(2)
   as
   begin
       select col1, null
       from rpctestB
       where col1 = @sNo
   end
				
   On server A
   -----------
				
   exec insert_rpctestA

At this point, you will receive the following error:
Msg 257, Level 16, State 1
Implicit conversion from datatype 'int' to 'varchar' is not allowed.
Use the CONVERT function to run this query.
Modification Type:MinorLast Reviewed:3/1/2005
Keywords:kbBug kbusage KB172335
©2004 Microsoft Corporation. All rights reserved.