August 1, 2006 at 7:17 am
Hi,
I'm using a couple of linked servers.
I want to create a stored procedure on all of the linked servers in a database with a name which exists on all of the linked servers.
For executing SQL on all of the linked servers I'm using:
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
while @x <= (select max(id) from #databases)
begin
select @dbname = name from #databases where id = @x
select @sql='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]'
execute @sql
set @x = @x + 1
end
drop table #databases
Is it possible to use a ‘create procedure’ in this construction?
Can anybody give me some help how to create a proper syntax for it?
Any help is kindly appreciated!
August 1, 2006 at 7:42 am
What happened when you tried the first and second time?
N 56°04'39.16"
E 12°55'05.25"
August 1, 2006 at 3:05 pm
I've tried something similar in the past. I don't recall my full experience, but I've got some ideas. I think the key was using linkedserver.master.dbo.sp_executesql, and including a USE databasename GO at the top of the batch I fired using sp_executesql.
Since you're table-driving it, you will most likely need to add another layer, and build a string to call sp_executesql locally that will call sp_executesql with the linked server name and database name from your table.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply