Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/171875

From BetaArchive Wiki
Knowledge Base


BUG: INSERT EXECUTE Statement May Fail with Error 213

Article ID: 171875

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q171875

BUG #: 17040 (NT: 6.5)

SYMPTOMS

If you use the INSERT EXECUTE statement and specify a column list to insert into a table with a timestamp column, and the stored procedure called does not return a value for the timestamp column in the result set, the INSERT statement may fail with following error:

Msg 213, Level 16, State 1
Insert error: column name or number of supplied values does not match
table definition.


The following scenario demonstrates this problem:

   CREATE TABLE t1 (
      test1 int NOT NULL ,
      test2 timestamp NOT NULL ,
      )
   GO

   CREATE TABLE t2 (
      test1 int NOT NULL ,
      test2 timestamp NOT NULL ,
      )
   GO

   CREATE PROCEDURE sp_1 AS
       select test1 from t1
   GO

   insert into t1 values (1,null)
   insert into t1 values (2,null)
   insert into t1 values (3,null)
   GO

   insert into t2 (test1) exec sp_1
   GO
                


The INSERT fails with the error message described above.

WORKAROUND

To work around this problem, modify the stored procedure to return a NULL value for the timestamp column, as in the following example:

   CREATE PROCEDURE sp_1 AS
   select test1, null from t1
   GO
                

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


Additional query words: Transact-SQL transql tsql

Keywords: kbbug kbusage KB171875