SP on linked server? - 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

Transact-SQL (2000)

SP on linked server?

Author

Topic

EA
Starting Member

19 Posts

Posted - 2006-07-24 : 09:10:46

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.

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 09:15:20

execute (@SQL)

Kristen