Dynamic sql in cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic sql in cursor

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-25 : 09:23:10

Hello All,

I have to get the output of sp_helprotect for each database on a server in temp tables. I have prepared a dynamic sql using cursor to go through each database and exec the sp_helprotect.

But I get the following error: @sql print statements are correct.

Msg 2812, Level 16, State 62, Line 33
Could not find stored procedure 'INSERT INTO #helprotectASPState exec sp_helprotect '.
Msg 2812, Level 16, State 62, Line 39
Could not find stored procedure 'ALTER Table #helprotectASPState ADD DBName varchar(100) '.
Msg 2812, Level 16, State 62, Line 45
Could not find stored procedure 'UPDATE #helprotectASPState SET DBName = '@DBName''.
Msg 2812, Level 16, State 62, Line 33

Here is the query:
declare @sql varchar(4000),
@DBName varchar(100),
@sql1 varchar(1000)

declare database_cursor cursor
for select name from master..sysdatabases
order by name
for read only

open database_cursor
fetch next from database_cursor into @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
select @sql =
'CREATE TABLE #helprotect' + @DBName +
' ( ' +
'owner varchar(10), ' +
'object varchar(100), ' +
'grantee varchar(100), ' +
'grantor varchar(100), ' +
'protecttype varchar(100), ' +
'action varchar(100), ' +
'columna varchar(100) )'
--print @sql
exec (@sql)
select @sql = ''

select @sql =
'INSERT INTO #helprotect' + @DBName +
' exec sp_helprotect '
--print @sql
exec @sql

SELECT @sql = ''
SELECT @sql = 'ALTER Table #helprotect' + @DBName +
' ADD DBName varchar(100) '
--print @sql
exec @sql
select @sql = ''
select @sql =
'UPDATE #helprotect' + @DBName +
' SET DBName = ' + '''@DBName'''
--print @sql
EXEC @sql

FETCH NEXT FROM database_cursor INTO @DBName

end

close database_cursor
deallocate database_cursor

Your help would be greatly appreciated.

Thanks in advace,
-P

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-25 : 09:55:51

temp tables nly apply to the scope you are in

each exec of dynamic sql is in its own scope

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam