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

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

--
27 Apr 2010 06: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

WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

open access

fetch access into @name

while (@@fetch_status = 0)

begin

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

EXEC(@SQL)

-- get next record

fetch access into @name

end

close access

deallocate access

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

gunneyk
New Member
New Member

--
02 May 2010 04: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
to
SET @SQL = 'USE [' + @name + '] ; '

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


Acceptable Use Policy
---