Geek asks and answers

Associated Server Query / Dynamic SQL

I currently have a linked server that I request in a stored procedure. I have a request that works fine now, however this request will need to be changed for each code branch that I have. I would like to know that the best method for derrive is the name of the database that I am calling in a cross server request.

Example: 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 of them is associated with its own server instance B ... 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 sproc is the choice of a cross server. So SRV_A.DB1_Dev has a choice in sproc that states:

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

while the stored procedure on the trunk branch will 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 a database for each mentioned branch, I would like to be able to dynamically populate the database name. The solution I developed is to use a series of IF checks with the CHARINDEX function, and then create a query with dynamic SQL, for example:

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 suggest that there is a better solution, although if someone could help me with it, it would be very appreciated. If some external shot is the best way, let me know.

Thanks James

+3
source share
2 answers

:
. - . , foo_table dbo

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


SELECT foo FROM dbo.syn_foo_table WHERE bar = 1

script, (), / . SQL- , , ( ).

SQLCMD script, VSA (AFAIK) SQLCMD .
SQL- $(variablename) - :

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

-v. . SQLCMD MSDN .

+5

, db, SRV, :

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

More articles:


All Articles