anushahanna
asked on
EXECUTE cannot be used as a source when inserting into a table variable.
the following format works in 2005, not in 2000...
what would be the equivalent of the same in 2000?
insert @test
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect')
*@test is a table variable
*@database_name is a temp variable
what would be the equivalent of the same in 2000?
insert @test
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect')
*@test is a table variable
*@database_name is a temp variable
Yep, you can't do that in 2005. 2008 you can I believe, but not 2005. Use temp tables instead.
ASKER
no, i meant to say
insert @test
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect')
works in 2005, not in 2000.
how would you do it in 2000?
insert @test
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect')
works in 2005, not in 2000.
how would you do it in 2000?
Just a sql server 2000 limitation.
#Temp works, so a 2-step process will work
if object_id('tempdb..#test') is not null drop table #test;
declare @database_name varchaR(100) set @database_name = 'tempdb'
declare @test table (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
create table #test (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
insert #test
EXEC master.dbo.sp_helprotect
insert @test select * from #test
select * from @test
#Temp works, so a 2-step process will work
if object_id('tempdb..#test')
declare @database_name varchaR(100) set @database_name = 'tempdb'
declare @test table (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
create table #test (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
insert #test
EXEC master.dbo.sp_helprotect
insert @test select * from #test
select * from @test
ASKER
in other words,
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect') would work wtih regardless of temp table or table variable.. in 2005..
is there a way to do it in one sentence like that in 2000...
EXEC ('EXEC ' + @database_name + '.dbo.sp_helprotect') would work wtih regardless of temp table or table variable.. in 2005..
is there a way to do it in one sentence like that in 2000...
I'd use temp tables. :)
Sorry, was testing, this is the complete one including your @database_name variable
if object_id('tempdb..#test') is not null drop table #test;
declare @database_name varchaR(100) set @database_name = 'master'
declare @test table (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
create table #test (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
declare @sql varchar(1000)
set @sql = 'insert #test EXEC [' + @database_name +'].dbo.sp_helprotect'
exec(@sql)
insert @test select * from #test
select * from @test
if object_id('tempdb..#test')
declare @database_name varchaR(100) set @database_name = 'master'
declare @test table (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
create table #test (owner sysname, object sysname, grantee sysname, grantor sysname, protecttype sysname, action sysname, [column] varchar(1000))
declare @sql varchar(1000)
set @sql = 'insert #test EXEC [' + @database_name +'].dbo.sp_helprotect'
exec(@sql)
insert @test select * from #test
select * from @test
> I'd use temp tables. :)
Yes, so would I.. haha
Yes, so would I.. haha
ASKER
Oh-- perfect..
so there are 2 limitations, actually?
the exec within the exec part...
and
table variable, too, right?
so there are 2 limitations, actually?
the exec within the exec part...
and
table variable, too, right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
y'all excellent!