July 24, 2006 at 7:11 am
I've created a stored procedure on several servers. All with the same name and all in a database with same names.
Now I’m trying to execute the stored procedures by executing a script.... I don’t get it to work!
I can execute it successfully by starting it manually:
exec [CIMPDB01\CIMT1].zz_am.dbo.usp_SpaceMon
exec [CIMPDB01\CIMT2].zz_am.dbo.usp_SpaceMon
and so on..... no problem
But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it won’t:
declare @x int
declare @dbname varchar(500)
declare @sql nvarchar(600)
set @x = 1
create table #databases
(ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances where actief='J'
while @x <= (select max(id) from #databases)
begin
select @dbname = name from #databases where id = @x
print @dbname
select @sql='exec ' + @dbname + '.zz_am.dbo.usp_SpaceMon'
print @sql
execute @sql
set @x = @x + 1
end
drop table #databases
Msg 203, Level 16, State 2, Line 17
The name 'exec [CIMPDB01\CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.
July 25, 2006 at 4:19 am
Here is just a wild guess (probably wrong), but I was wondering if it could be security related. Perhaps when you run the SP the linked server knows it is you running it and that you have the right to do so, but that when the script runs it perhaps it doesn't?
July 25, 2006 at 9:32 am
It looks like there are 2 executes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply