Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006

    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]'
    execute @SQL
    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?

    Any help is kindly appreciated!

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    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.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts