4

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.

sergiogarciadev
  • 2,061
  • 1
  • 21
  • 35
  • There are development, acceptance, and production db's all on the SQL 2000 instance? Do all three db's have the same structure (same tables, views, etc.)??? – Dave Mason May 06 '14 at 20:01
  • Exactly same tables, views, etc. Different instances and data. I have only access to development and acceptance. The script will be executed by the DBA using the production server. – sergiogarciadev May 06 '14 at 20:07
  • Is "Lazy Schema Validation" set to true for the linked server? This is kind of a lame stab in the dark, but it's worth trying. BTW, you need Enterprise or Developer Edition for this feature. – Dave Mason May 06 '14 at 20:49
  • Unfortunately I'm using "Microsoft SQL Server 2000 SP4 Standard Edition" for the legacy server which doesn't provide this option. In the new server I have "Microsoft SQL Server 2012 SP1 Enterprise (64-bit)". – sergiogarciadev May 07 '14 at 13:13

1 Answers1

6

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.

qxg
  • 6,955
  • 1
  • 28
  • 36
  • It gives me the same problem I currently have and include new ones. I will need to change the script that create the synonyms and will have a lot of new objects to remove. – sergiogarciadev May 07 '14 at 12:30
  • I will give a plus one and accept you answer because it give me a good idea. Actually I you query `[legacyserver].[database].[schema].[sysobjects]` and retrieve all the tables, place a synonyms for each of them in a schema called legacy in the new db, import the data and them remove the legacy schema with everything, making my new db clean. – sergiogarciadev May 09 '14 at 13:59