Sign up ×
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:

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
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

2 Answers 2

up vote 14 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',
    @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.

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

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
share|improve this answer
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
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


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.