|
|
Error Message
Server: Msg 197, Level 15, State 1, Line 1
EXECUTE cannot be used as a source when inserting into a
table variable.
Causes:
As the message suggests, you are trying to insert the result set returned by a stored procedure into a table variable, which is not allowed in SQL Server.
|
To illustrate how this error is encountered, here’s a script that will generate the error, which simply inserts the result set returned by the sp_helpdb system stored procedure into a local table variable called @Databases (the sp_helpdb system stored procedure reports information about a specified database or all databases in master.dbo.sysdatabases table):
DECLARE @Databases TABLE (
[DatabaseName] VARCHAR(50),
[DB_Size] VARCHAR(20),
[Owner] VARCHAR(50),
[DBID] INT,
[CreationDate] DATETIME,
[Status] VARCHAR(1000),
[CompatibilityLevel] INT
)
INSERT INTO @Databases
EXECUTE [dbo].[sp_helpdb]
Executing this script in Query Analyzer will generate the following errors:
Server: Msg 197, Level 15, State 1, Line 12
EXECUTE cannot be used as a source when inserting into a table variable.
Solution / Work Around:
To avoid this error, use a local temporary table instead of using a table variable. Here’s the same script but using a local temporary table instead of a table variable:
CREATE TABLE #Databases (
[DatabaseName] VARCHAR(50),
[DB_Size] VARCHAR(20),
[Owner] VARCHAR(50),
[DBID] INT,
[CreationDate] DATETIME,
[Status] VARCHAR(1000),
[CompatibilityLevel] INT
)
INSERT INTO #Databases
EXECUTE [dbo].[sp_helpdb]
SELECT * FROM #Databases
|