Wednesday, December 06, 2017

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  --


 create table #table1( 
   [col1] varchar(255) null, 
   [col2] varchar(255) null, 
   [col3] varchar(100) null,
   [col4] varchar(6) null,
            [col5] int null
            )

 Insert into #table1
 (
 [col1] ,
   [col2], 
   [col3] ,
   [col4] ,
            [col5]
 )
 EXEC [dbo].[stored_proc1]
 @Region = N'Canada'

I got the error as below:
Msg 556, Level 16, State 3, Line 36
INSERT EXEC failed because the stored procedure altered the schema of the target table.

When I just run the stored procedure without inserting into the temp table, the stored procedure runs fine and gives the results.

I tried to look at the order of the columns of the temp table I am inserting into, and also the temp table column configurations.  Everything looked the same.

So I started investigating the code in the stored procedure.  I found one clue that could be causing this.  The temp table in the store procedure has the same name as the temp table I am inserting the results of the stored procedure
So I changed the temp table name to table2 and then the results of the stored procedure were inserted into the new temp table table2.

So the final code is as follows:



 create table #table2( 
   [col1] varchar(255) null, 
   [col2] varchar(255) null, 
   [col3] varchar(100) null,
   [col4] varchar(6) null,
            [col5] int null
            )

 Insert into #table2
 (
 [col1] ,
   [col2], 
   [col3] ,
   [col4] ,
            [col5]
 )
 EXEC [dbo].[stored_proc1]
 @Region = N'Canada'

1 comment:

Archit...... the SAP freak said...

Hello, I tried this solution but didn't worked. The issue finally solved when I made Global Temporary Table. For example. Previously my table was #TestTable I changed this to ##TestTable and query run successfully.


Anyone facing same issue may try what I did.


Thanks

Free Power BI Classes for Week 7 (Last)

Just completed the last class of the F ree  P ower BI  classes of this series.  Today we have covered Adding a canvas background Adding Imag...