CODEWITHSUNDEEP
sqlt-sqlstored-proceduressql-server-2008-r2dynamic-sql
SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

Linked Server Query / Dynamic SQL

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

Upvotes: 3

Views: 3828

Answers (2)

SSISPissesMeOff
SSISPissesMeOff

Reputation: 450

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)

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21505

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.

Upvotes: 5

Related Questions