Unanswered: Create (U)SP in database on linked server
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)
select @dbname = name from #databases where id = @x
select @SQL='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]'
set @x = @x + 1
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?
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.
If it's not practically useful, then it's practically useless.