sql server execute command in multiple server

Question

I have several link server on my server (about 20 servers). on these server exists a similar database and I want to execute a stored procedure on these server in depend of a parameter on my stored procedure on my server. I have following query for this idea.

Create procedure test
    (
       @ServerName VarChar(100),
       @Code int
    )
As
    IF(@ServerName = 'Server1')Begin
       Exec Server1.SGDB.dbo.DocInsert @Code
    End Else IF(@ServerName = 'Server2')Begin
       Exec Server2.SGDB.dbo.DocInsert @Code
    End Else IF(@ServerName = 'Server3')Begin
       Exec Server3.SGDB.dbo.DocInsert @Code
    .
    .
    .
    End Else IF(@ServerName = 'Server20')Begin
       Exec Server20.SGDB.dbo.DocInsert @Code
    End
End

Can I have a dynamic query with @ServerName parameter without using exec(string command).

thanks in advance.

Szymon · Accepted Answer · 2014-04-05 11:54:25Z

1

You can replace the body of your stored procedure with this dynamic SQL:

declare @sql nvarchar(max)

set @sql = N'Exec ' + @ServerName + N'.SGDB.dbo.DocInsert @Code'

exec sp_executesql  @stmt = @sql, @params = N'@Code int', @Code = @Code

It will be also better to declare the parameter @ServerName as NVarChar(128) to always work on Unicode characters and to allow for the maximum length of a linked server name.

edited Apr 5, 2014 at 11:54

answered Apr 5, 2014 at 11:49

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql server execute command in multiple server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related