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:

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?

  • 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, 2012 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. Oct 10, 2012 at 14:56
  • @jnk, you should make that an answer.
    – HLGEM
    Oct 10, 2012 at 17:10
  • For many linked-servers at once
    – it3xl
    Jan 30, 2018 at 15:57

2 Answers 2


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',
    @provstr=N'DRIVER={SQL Server};SERVER=ACTUALSERVERNAME;UID=user1;PWD=rosebud567;', 

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


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

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

Exec  @sql
  • 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, 2012 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, 2012 at 18:06
  • Good point @Eli
    – HLGEM
    Oct 10, 2012 at 18:40
  • Trying this with SET @sql = 'DELETE FROM ' + @linkedservername + '..Sthing.Table WHERE DOSSIER_ID LIKE ''00%''';, I do have the error Could not find server 'DELETE FROM lnk_stga' in sys.servers... while the request is correct! If I print it, I can copy it and execute it without problems! Sep 26, 2020 at 12:11
  • Why is it looking for DELETE FROM lnk_stga as a server name? Sep 26, 2020 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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