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!Your code assumes that the database IDs are sequential and continuous, which they are probably not, so you are going to generate a lot of errors with this. But as far as creating the sprocs, your dynamic SQL will need to start with a USE statement to set the scope to your target database.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment