When using remote server connections in MSSQL they need to be in the format select * from [ServerName].[DatabaseName].[SchemaName].[TableName]. If you want to switch between two servers (say Production and Test) you only need to recreate the LinkedServer. The problem arises when the database and or schema name on the linked server is different. [ProductionData].[Pub] -> [TestData].[dbo]. This change would mean that you have to change every query which reference to the linked server.
A solution is using Synonyms instead. This means that you can use select * from [SynonymSchemaName].[Synonym] instead. The big advantage is that you can script the creating of synonyms, so a database connection switch can be done in seconds rather then hours.
This article contains a csp_ (Ciber Stored Procedure) for this task.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: John Minkjan
-- Create date: 20090223
-- Description: Automaticly creates synonyms for
-- table in (remote) database
-- v001 - initial version
-- =============================================
alter PROCEDURE csp_CreateSynonyms (
-- Add the parameters for the stored procedure here
@SeverName varchar(255),
-- holds the remote servername (optional)
@DatabaseName varchar(255),
-- holds the remote databasename ( NOT optional)
@SchemaName varchar(255),
-- holds the remote schemaname (Default: dbo)
@TargetSchemaName varchar(255),
-- holds the target schemaname for the synonyms (default: dbo)
@ReturnMessage varchar(400) output
)
AS
declare @sqlString varchar(4000)
-- holds the native SQL
declare @TableName varchar(255)
-- holds the TableName
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Cleanup @Servername
if ( ltrim(rtrim(@SeverName)) is null or
ltrim(rtrim(@SeverName)) = '' )
begin
set @SeverName = ''
end
else
begin
set @SeverName = ltrim(rtrim(@SeverName)) + '.'
end
-- Cleanup DataBase Name
set @DatabaseName = ltrim(rtrim(@DatabaseName)) + '.'
-- Cleanup @SchemaName
if ( ltrim(rtrim(@SchemaName)) is null or
ltrim(rtrim(@SchemaName)) = '' )
begin
set @SchemaName = 'dbo'
end
else
begin
set @SchemaName = ltrim(rtrim(@SchemaName))
end
-- Cleanup @TargetSchemaName
if ( ltrim(rtrim(@TargetSchemaName)) is null or
ltrim(rtrim(@TargetSchemaName)) = '' )
begin
set @TargetSchemaName = 'dbo'
end
else
begin
set @TargetSchemaName = ltrim(rtrim(@TargetSchemaName))
end
-- Set the Cursor
set @sqlString = 'declare SynonymCur cursor for ' +
'SELECT t1.name AS TableName ' +
'FROM (SELECT * '+
'FROM '+ @SeverName + @DatabaseName + 'sys.objects) AS t1 INNER JOIN ' +
'(SELECT * ' +
'FROM '+ @SeverName + @DatabaseName + 'sys.schemaS) AS t2 ON t1.schema_id = t2.schema_id '+
'WHERE (t1.type = ''U'') and t2.name = '+ CHAR(39) + @SchemaName + CHAR(39)
exec (@sqlString)
Open SynonymCur
fetch next from SynonymCur into @TableName
while @@fetch_status=0
begin
-- Create and Execute Drop Statemen
set @sqlString = 'IF EXISTS (SELECT * FROM ' +
'sys.synonyms WHERE name = N'+ CHAR(39)+ @TableName+ ''') ' +
'DROP SYNONYM ['+ @TargetSchemaName + '].[' + @TableName+ '] '
exec (@sqlString)
-- Create and Execute Create Statement
set @sqlString = 'CREATE SYNONYM ['+ @TargetSchemaName + '].[' + @TableName+ '] ' +
'FOR '+ @SeverName + @DatabaseName + '['+ @SchemaName + '].[' + @TableName+ '] '
PRINT @sqlString
exec (@sqlString)
fetch next from SynonymCur into @TableName
end
-- Clean up
close SynonymCur
deallocate SynonymCur
-- Feedback
set @ReturnMessage = 'Succes'
END
GO
Till Next Time
