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:
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
Post a Comment