EXECUTE cannot be used as a source when inserting into a table variable.

Last Post 02 May 2010 03:33 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

27 Apr 2010 05:41 AM
I have a dynamic SQL statement in SQL 2000 that returns a Msg 197 on the INSERT INTO line.  The code follows:

DECLARE @encrypt table ([dbname] sysname)
declare @name sysname, @SQL nvarchar(1000), @CNT INT

declare access cursor

for SELECT name

FROM [master].dbo.sysdatabases


open access

fetch access into @name

while (@@fetch_status = 0)


SET @SQL = 'USE [' + @name + ']'

SET @SQL = 'SELECT u.name

FROM sysprotects p, sysusers u

WHERE p.uid = u.uid

AND p.action = 203'

INSERT INTO @encrypt


-- get next record

fetch access into @name


close access

deallocate access

Any assistance to resolve this would be appreciated.  Thank you.

New Member
New Member

02 May 2010 03:33 PM
Use a temp table instead that is created once before the dynamic sql calls. Also you need to fix this line:

SET @SQL = 'USE [' + @name + ']'

SET @SQL = 'SELECT u.name
SET @SQL = 'USE [' + @name + '] ; '

SET @SQL = @SQL + 'SELECT u.name

Acceptable Use Policy