9

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?

4
  • 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
  • For many linked-servers at once – it3xl Jan 30 '18 at 15:57
14

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.

0
0

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
5
  • 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
  • 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! – user3341592 Sep 26 '20 at 12:11
  • Why is it looking for DELETE FROM lnk_stga as a server name? – user3341592 Sep 26 '20 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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