Conditional SQL targeting different servers

Question

How can I have a single piece of SQL conditionally change the target server ?

So on my local dev machine, I can run it and have the command ...

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [DEVSERVER].[cases].[dbo].[sp_class]
        WHERE   [status] = 1

But on the test machine I want the same piece of SQL to run and have the command ...

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [TESTSERVER].[cases].[dbo].[sp_class]
        WHERE   [status] = 1

I've tried using a variable @TGT and setting it to either devserver or testserver with the SQL FROM [@TGT].[cases].[dbo].[sp_class], but I get an error Could not find server '@TGT' in sys.servers.

Answer 1

You would need to use dynamic SQL. Also you should not be using 'single quotes' to delimit column aliases; this syntax has been deprecated. You should be using [square brackets] or "double quotes" (the former generally preferred).

DECLARE @sql NVARCHAR(MAX), @TGT SYSNAME;

SET @TGT = N'[TESTSERVER]';

SET @sql = N'INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS [Team number] ,
                [description] AS [Team name] ,
                [DutyEmail] AS [Team email address]
        FROM    ' + @TGT + '.[cases].[dbo].[sp_class]
        WHERE   [status] = 1;';

PRINT @sql;
EXEC sp_executesql @sql;

(In fact you don't really need the aliases here, though you should be specifying the insert column list.)

Or, what you can do to make the code more stable, is to use synonyms in your two environments. In dev:

CREATE SYNONYM dbo.sp_class FOR DEVSERVER.cases.dbo.sp_class;

In Test:

CREATE SYNONYM dbo.sp_class FOR TESTSERVER.cases.dbo.sp_class;

Now your query can simply be:

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class],
                [description],
                [DutyEmail]
        FROM    [dbo].[sp_class]
        WHERE   [status] = 1;

Of course synonyms only work if you are on SQL Server 2005 or better. It is always useful to specify the version of SQL Server you are using!

Answer 2

I usually use Synonyms for this kind of situation. Since you are running this from two different places, you just need to have your synonym defined appropriately in each place.

On your local dev machine:

CREATE SYNONYM dbo.Cases_sp_class FOR [DEVSERVER].[cases].[dbo].[sp_class]

And on your test machine:

CREATE SYNONYM dbo.Cases_sp_class FOR [TESTSERVER].[cases].[dbo].[sp_class]

Then you would just need to change your SQL to this in all environments:

INSERT  INTO [sds].[dbo].[Team_Email_Addresses]
        SELECT  [class] AS 'Team number' ,
                [description] AS 'Team name' ,
                [DutyEmail] AS 'Team email address'
        FROM    [dbo].[Cases_sp_class]
        WHERE   [status] = 1

A couple of notes:

1. TESTSERVER and/or DEVSERVER need to be defined as Linked Servers
2. If [sp_class] is really a stored procedure, you will need to do all of this a slightly different way. If it is not, you should probably not be using the "sp_" prefix for it as that is the prefix that MS uses for it's own system procedures which can get very confusing.