Error Message:
Msg 556, Level 16, State 3, Line 2
INSERT EXEC failed because the stored procedure altered the schema of the target table.
Severity level:
16.
Description:
This error message appears when you try to execute an INSERT…EXEC statement in which the procedure in the EXEC part alters the table definition of the table references in the INSERT part.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. The statement can not be run this way. You can not alter the table definition referenced in the INSERT part in the procedure referenced in the EXEC part of an INSERT…EXEC statement.
Versions:
All versions of SQL Server.
Example(s):
IF OBJECT_ID(‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
c1 int
);
GO
IF OBJECT_ID(‘dbo.p’) IS NOT NULL
DROP PROCEDURE dbo.p;
GO
CREATE PROCEDURE dbo.p
AS
ALTER TABLE dbo.t ADD c2 int;
SELECT 1;
GO
INSERT INTO dbo.t EXEC dbo.p;
Remarks:
In the above example we try to use the procedure dbo.p to INSERT…EXEC into dbo.t. Because dbo.p alters the definition of dbo.t, the error is raised.