Create (U)SP in database on linked server

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)

Create (U)SP in database on linked server

Author

Topic

EA
Starting Member

19 Posts

Posted - 2006-08-01 : 09:11:40

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!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-01 : 09:38:37

I think it would have to be something like

declare @sql varchar(8000)
select @sql = 'create proc mrproc as .....'
select @sql = 'exec ' + @svr + '.' + @db' + 'dbo.sp_executesql + N''' + replace(@sql,'''','''''') + ''''
exec (@sql)

It's executing sp_executesql on the remote server with the create proc command.
Another way is to put the create proc script in a file and use osql to execute it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources