How do I write portable SQL that refers to a linked server?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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