I have two database servers, server1 and server2, which are our diff environment of our legacy system. I have to write a stored procedure for getting data which is communicate with one of the env.
I want a way so that I can easily switch the env
Like:
Declare @Server nvarchar(20)
set @Server="server1"
select *
from @Server.global.dbo.tblaccount
Parameters cannot be used for identifiers -- column names, table names, schema names, server names, function names, and so on.
You can do this with dynamic SQL:
declare @Server nvarchar(20);
declare @sql nvarchar(max);
set @Server = 'server1';
set @sql = 'select * from [Server].global.dbo.tblaccount';
set @sql = replace(@sql, '[Server]', @Server);
exec sp_executesql @sql;
use synonyms:
create synonym mylocalschema.RemoteTable for server1.database.schema.table;
to be referenced as:
select top 10 * from mylocalschema.RemoteTable;
depending on env the synonyms will be a reference to the right server and the code (sp, views, triggers, whatever) will be the very same for both.
the only duplicate item is the script to create synonyms: one for server1 and another copy for server2 (or maybe the same script built with dynamic sql...).
