Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following:

INSERT INTO [TableName]
(...Columns...)
SELECT ...Columns...
FROM [ServerName\InstanceName].[Catalogue].[dbo].[TableName]
WHERE TableNameID = @TableNameID

This procedure exists in my Development environment, Test environment and Live environment.

The problem is that each copy of the procedure is subtly different because the server names are different for each environment. This makes managing deployment of script updates troublesome.

Is there a way to make the procedure portable so that each environment can run identical versions of it?

If not, is there anything I can do to make script deployment less prone to mistakes/errors?

share|improve this question
3  
Is creating a view that differs on each server an option? You could define the view as SELECT <fields> FROM <linked server> but use the same view name across all servers to keep code maintained –  JNK Oct 10 '12 at 14:38
    
@JNK that's not a bad idea, there are quite a few tables though, but at least views would be simpler to maintain than a stored procedure with linked server references peppered throughout. –  Doctor Jones Oct 10 '12 at 14:56
    
@jnk, you should make that an answer. –  HLGEM Oct 10 '12 at 17:10

3 Answers 3

up vote 13 down vote accepted

The name of your linked server doesn't have to be the server's name. You can use a generic name.

EXEC master.dbo.sp_addlinkedserver
    @server = N'COMMONNAME',
    @srvproduct=N'MSDASQL',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;', 
    @catalog=N'database1'

Set up the linked server on each environment with the same name, but actually point them at different servers.

share|improve this answer
    
+1 exactly what I'd do –  gbn Oct 11 '12 at 7:02

You could create a system to run these stored procedures. I've created some sample code here that I tested quickly.

/*
    This table is the same on every server, and contains the source Stored Proc with 
    variables such as [LinkedServer].[LinkedDatabase].[LinkedSchema].[LinkedTable] 
    that will be replaced by the RunProc stored proc below.
*/
CREATE TABLE DistributedProcs
(
    DistributedProcID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT
        PK_DistributedProcs IDENTITY(1,1)
    , ProcName nvarchar(255)
    , ProcText nvarchar(max)
);

/*
    This is a test stored proc that can be ran against any linked server.
*/
INSERT INTO DistributedProcs (ProcName, ProcText) 
    VALUES ('TestProc',
    'SELECT * from [LinkedServer].[LinkedDatabase].[LinkedSchema].[LinkedTable]');

/*
    This table contains replacements for 
    [LinkedServer].[LinkedDatabase].[LinkedSchema].[LinkedTable] for
    each stored procedure in DistributedProcs
*/
CREATE TABLE DistributedProcVars
(
    DistributedProcVarsID INT NOT NULL PRIMARY KEY CLUSTERED 
        CONSTRAINT PK_DistributedProcVars IDENTITY(1,1)
    , DistributedProcID INT
    , LinkedServer nvarchar(255)
    , LinkedDatabase nvarchar(255)
    , LinkedSchema nvarchar(255)
    , LinkedTable nvarchar(255)
);

INSERT INTO DistributedProcVars (DistributedProcID, LinkedServer, 
    LinkedDatabase, LinkedSchema, LinkedTable)
VALUES (1, 'MyServer','MyDatabase','sys','tables');

GO

/*
    This stored procedure runs the actual stored procedure contained 
    in DistributedProcs
*/
CREATE PROCEDURE RunProc
(
    @ProcNameToRun nvarchar(255)
)
AS
BEGIN
    DECLARE @ProcID INT;
    DECLARE @LinkedServer nvarchar(255);
    DECLARE @LinkedDatabase nvarchar(255);
    DECLARE @LinkedSchema nvarchar(255);
    DECLARE @LinkedTable nvarchar(255);
    DECLARE @ProcText nvarchar(max);
    SELECT @LinkedServer = [LinkedServer]
        , @LinkedDatabase = [LinkedDatabase]
        , @LinkedSchema = [LinkedSchema]
        , @LinkedTable = [LinkedTable]
        , @ProcText = [ProcText]
    FROM DistributedProcs DP 
        INNER JOIN DistributedProcVars DPV ON 
                DP.DistributedProcID = DPV.DistributedProcID
    WHERE DP.ProcName = @ProcNameToRun;
    SET @ProcText = REPLACE(@ProcText, '[LinkedServer]',@LinkedServer);
    SET @ProcText = REPLACE(@ProcText, '[LinkedDatabase]',@LinkedDatabase);
    SET @ProcText = REPLACE(@ProcText, '[LinkedSchema]',@LinkedSchema);
    SET @ProcText = REPLACE(@ProcText, '[LinkedTable]',@LinkedTable);
    EXEC sp_executesql @ProcText;
END
GO

SELECT * FROM DistributedProcs;
SELECT * FROM DistributedProcVars;
EXEC RunProc 'TestProc';

This creates a table called DistributedProcs that contains the text of the stored procedures that you want to run on multiple servers. You'd create this table on each server, along with DistributedProcVars to store the names of the servers/databases/schemas/tables for each stored proc.

Then instead of running the actual stored proc, you run RunProc with the ProcName of the stored procedure you want executed.

Not perfect, but does what you need, I believe.

share|improve this answer

I like the idea of using a generic linked server name. However, in many environments this may not be possible, In this case, you can use dynamic SQl in your sp.

declare @linkedservername nvarchar(200)
declare @sql nvarchar(4000)
SET @linkedservername =  CASE @@ServerName  
                            WHEN 'DevServer' THEN 'LinkedServerForDevEnvironment'
                            WHEN 'TestServer' THEN 'LinkedServerForTestEnvironment'
                            WHEN 'ProdServer' THEN 'LinkedServerForProdEnvironment'
                            ELSE Null
                        END   

set @sql = 'INSERT INTO [TableName] 
(...Columns...) 
SELECT ...Columns... 
FROM ' + @linkedservername + '.[Catalogue].[dbo].[TableName] 
WHERE TableNameID = @TableNameID'

Exec  @sql
share|improve this answer
1  
I will note that if I was doing this in real life I would be using a try catch block and bubble up an error if @linkedservername was null after the set statement to warn you that this was run on the wrong server. –  HLGEM Oct 10 '12 at 17:09
1  
If you took this approach, I think I'd wrap the CASE statement in a function so if a server changed, I'd only have to update the function. –  Eli Oct 10 '12 at 18:06
    
Good point @Eli –  HLGEM Oct 10 '12 at 18:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.