How to fix Error 197 on Windows Server 2025 / Windows 11

by

Why This Error Happens

This error occurs because SQL Server’s T-SQL syntax does not permit the INSERT INTO @TableVariable EXEC(...) pattern in the same way it allows INSERT INTO #TempTable EXEC(...). The query optimizer cannot resolve the result set schema of a dynamic EXECUTE or stored procedure call directly into a table variable at compile time. This is a hard syntax restriction in the SQL Server engine (unchanged in SQL Server 2025) rather than a permissions or configuration issue, meaning it cannot be fixed via server settings—only by rewriting the query.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the query throwing Error 197.
  • Look specifically for a pattern similar to: INSERT INTO @MyTableVar EXEC dbo.MyStoredProc;
  • Confirm the target variable is declared with DECLARE @MyTableVar TABLE (...).

Step 2: Replace the Table Variable with a Temporary Table

  • Since temp tables support EXEC as an insert source, change the declaration from a table variable to a #temp table.
  • Update your code as follows:
  CREATE TABLE #MyTempTable (Col1 INT, Col2 VARCHAR(50));
  INSERT INTO #MyTempTable
  EXEC dbo.MyStoredProc;
  • Ensure the temp table schema (#MyTempTable) exactly matches the columns and data types returned by the stored procedure or dynamic SQL.

Step 3: Migrate Data Back to a Table Variable (If Required)

  • If a table variable is strictly required downstream (e.g., for a Table-Valued Parameter), perform a secondary insert from the temp table into the table variable.
  • Example:
  DECLARE @MyTableVar TABLE (Col1 INT, Col2 VARCHAR(50));
  INSERT INTO @MyTableVar
  SELECT Col1, Col2 FROM #MyTempTable;
  • Drop the temporary table at the end of the batch using DROP TABLE #MyTempTable; to free tempdb resources immediately.

Step 4: Validate Compatibility Level in SQL Server 2025

  • Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName'; to check if the database is running in an older compatibility mode.
  • While this specific restriction persists across all compatibility levels, verifying you are on the latest level (170 for SQL Server 2025) ensures other newer T-SQL enhancements (like inline scalar UDFs) are available to optimize the rest of your script.

Alternative or Verification Steps

  • Verify the Fix: Execute the modified script in SSMS. If Error 197 no longer appears and the SELECT * FROM #MyTempTable or @MyTableVar returns the expected rows, the syntax rewrite was successful.
  • Alternative Solution: If modifying the stored procedure is possible, consider using an OUTPUT clause or a User-Defined Table Type (TVP) passed as a parameter instead of relying on EXEC to populate results, which avoids the temp table intermediary step entirely.