Linked Server Query / Dynamic SQL

Question

I currently have a linked server that I am querying in a stored procedure. I have the query working just fine currently however this query will need to change for every branch of code I have. I would like to know what the best method is for derriving the database name I am calling in the cross server query.

Ex: Server A has a link to server B. Server A contains 3 databases. SRV_A.DB1_DEV, SRV_A.DB2_Trunk, SRV_A.DB3_Prod Each are linked to their Server B counterpart... SRV_B.DB1_DEV, SRV_B.DB2_Trunk, SRV_B.DB3_Prod

Each database on Server A has the same stored procedure. The only thing that changes in the sproc is the cross server select. So SRV_A.DB1_Dev has a select in the sproc that reads:

SELECT foo FROM [SRV_B].[DB1_DEV].[foo_table] WHERE bar = 1

while the stored procedure on the trunk branch would be

SELECT foo FROM [SRV_B].[DB2_Trunk].[foo_table] WHERE bar = 1

Since I would like to have a VS project that will deploy the DB to every branch mentioned I would like to be able to fill in the database name dynamically. The solution I have came up with that is working is to use a series of IF checks with the CHARINDEX function, and then create the query with dynamic SQL, like this:

DECLARE @dSql NVARCHAR(4000);
DECLARE @databaseName NVARCHAR(100) = DB_NAME();
DECLARE @tableName NVARCHAR(100);
IF SELECT CHARINDEX('Dev', @databaseName, 0)
    SET @tableName = '[SRV_B].[DB1_DEV].[foo_table]
    ...Same if & set for Trunk
    ...Same if & set for Prod
SET @dSql = 'DECLARE @retID INT;SELECT foo FROM ' + @tableName 
+ ' WHERE bar = 1';SET @retID = SELECT SCOPE_IDENTITY()'
EXEC(@dSQL);

I would have to imagine there is a better solution though, if anyone can help me with one, it would be much appreciated. If by some outside shot this is the best way let me know as well.

Thanks, James

Ed Harper · Accepted Answer · 2012-04-16 08:53:13Z

5

One way to solve this problem might be to abstract the linked server name by wrapping it in a synonym:
note the extra part in the target table name - cross-server queries require a four-part name - I'm assuming this is a typo in the question and that foo_table is in the dbo schema

CREATE SYNONYM dbo.syn_foo_table
FOR [SRV_B].[DB1_DEV].[dbo].[foo_table]

which could then be referred to in the code as

SELECT foo FROM dbo.syn_foo_table WHERE bar = 1

You would then need to customise your deployment script to create the synonym(s) pointing at the correct server/database for the environment. This could use a similar dynamic SQL process to the one you've outlined above, but would only need to be executed once at deployment time (rather than on every execution).

Another possible solution is to use SQLCMD parameters in the stored procedure script, since (AFAIK) VS projects use SQLCMD to deploy database objects.
This feature allows you to parameterise SQL scripts with variables in the form $(variablename) - in your case:

SELECT foo FROM [SRV_B].[$(dbname)].[foo_table] WHERE bar = 1

The value of the variable can be set using an environment variable or passed into the command as an argument using the -v switch. See the SQLCMD MSDN link above for full details.

answered Apr 16, 2012 at 8:53

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SSISPissesMeOff Over a year ago

Awesome, both methods worked like a charm. I decided to go with option B. Yes the question was a typo I am indeed using 4 part naming. Thank you for the excellent explanation!

SSISPissesMeOff · Accepted Answer · 2012-04-17 01:41:32Z

0

I was able to use a combination of enviornment variables as mentioned above for the db-name, and also dynamically generate the SRV name as well by using the following query:

DECLARE @ServerName NVARCHAR(100);
SET @ServerName = (SELECT name FROM sys.servers WHERE server_id = 1)

answered Apr 17, 2012 at 1:41

SSISPissesMeOff

4601 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

Linked Server Query / Dynamic SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related