SQL Server Error Messages - Msg 197¶

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