EXECUTE cannot be used as a source when inserting into a table variable. (SQL 2K)

how can exec within exec be done in sql 2000?
LVL 5
25112Asked:
Who is Participating?
 
LowfatspreadCommented:
have you tried


EXEC (@DBName + '.dbo.sp_helprotect')

or

EXEC('EXEC ' + @DBName + '.dbo.sp_helprotect')  -- no space between exec and (

?

otherwise use the sp_execsql  stored procedure....
0
 
25112Author Commented:
the syntax works in 2005 not 2000
declare @perms table (
ObjectOwner varchar(50) NULL,ObjectName varchar(50) NULL,Grantee varchar(50) NULL,Grantor varchar(50) NULL,ProtectType varchar(50) NULL,[Privilege] varchar(50) NULL,[Column] varchar(10))
declare @DBName sysname
set @DBName = db_name()	

insert @perms
EXEC ('EXEC ' + @DBName + '.dbo.sp_helprotect')

select LTRIM(RTRIM(ProtectType))+' '+LTRIM(RTRIM(Privilege))+' '+' ON '+[ObjectName]+' TO '+Grantee+';
'  from @perms

WHERE ObjectOwner NOT IN('sys','.')  -- excludes sys objects
ORDER BY ProtectType

Open in new window

0
 
NOC_TrollCommented:
Another thing to look at is data types.  Looking at the statement you concatenated and passed to the EXEC function:
('EXEC ' + @DBName + '.dbo.sp_helprotect')
It begins with a VARCHAR, followed by data type sysname followed by another VARCHAR.

If you use CAST to change sysname to match the others it should work.

Try
('EXEC ' + CAST(@DBName AS VARCHAR(50)) + '.dbo.sp_helprotect')

Hope this helps.
0
 
keyuCommented:
no need to write exec command twise

try this

EXEC (CAST(@DBName AS VARCHAR(50))  + '.dbo.sp_helprotect')


or

Declare @str as varchar(250)

Set @str=CAST(@DBName AS VARCHAR(50))  + '.dbo.sp_helprotect'

Exec @str
0
 
25112Author Commented:
thanks for trying.. but i found that none of the options work with table variable in 2000
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.