CODEWITHSUNDEEP
sql-servert-sqllinked-server
sergiogarciadev
sergiogarciadev

Reputation: 2202

Is there a way to query SQL Server linked server without hardcoding database name?

I found samples where the code:

SELECT * FROM [legacyserver].[database].[schema].[table]

was expressed as:

SELECT * FROM [legacyserver]...[table]

but isn't working for me.

It gives me the error:

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "legacyserver".

I'm using for legacy server SQL SERVER 2000 and for the new server SQL SERVER 2012.

I tried creating the linked server using:

EXEC sp_addlinkedserver 
   @server = 'legacyserver', 
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=legacyserver;DATABASE=database;Trusted_Connection=Yes;',

and:

EXEC sp_addlinkedserver 
   @server = 'legacyserver', 
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=legacyserver;Trusted_Connection=Yes;',
   @catalog = 'database';

Is it possible to create the script without hard coding the database name?

I need to create a really big migration script and it need to use a development, acceptance and production databases, and it will be to much work and error prone to change it using replace in the text editor.

UPDATE:

The legacy development, acceptance and production databases are exactly the same (except for the data) and each one have it's own server/instance and database name.

Due to segregation of duties, I can't develop something and deploy it, so I have no way to touch this script after acceptance. I will need to instruct someone else to do so, and if he/she will need to replace every occurrence of [legacyserver].[database], the chances of mistakes are very high.

Upvotes: 4

Views: 4319

Answers (1)

qxg
qxg

Reputation: 7036

You can create a synonym 

CREATE SYNONYM [table] FOR [legacyserver].[database].[schema].[table]

When you query 

SELECT * FROM table

it's actually fetching data from linked server instead of local database.

If want to change database, just drop synonym and create a new one with new database name.

DROP SYNONYM table;
CREATE SYNONYM [table] FOR [legacyserver].[anotherdatabase].[schema].[table]

Your query statement is unchanged.

EDIT: DROP and CREATE SYNONYM statement is a little misleading. You don't need to do it yourself. It's one time job in deployment. Just create a Post-Deployment script that creates all synonyms and parametrize linked server name and database names. Code like:

IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'table1') 
BEGIN
DROP SYNONYM table1
END
EXEC('CREATE SYNONYM table1 FOR ' + '$(LinkedServerName).$(DBName).[dbo].[Table1]')

Note, it use SQLCMD syntax. Ask operations to change parameter in different environments.

Upvotes: 6

Related Questions