dev to UAT to Live using linked server catalog to default the database

Hi all,

Im trying to have one set of TSQL code that uses global settings in 'linked servers' so code can migrated from development, to test to UAT and finally to live without change. e.g.

select *
from [oursystem].ourDB.dbo.table
join [linked_server_othersystem]..dbo.tablename ...

By using no database name (and expecting the catalog set at the linked server to be used) the same code is equivilent to:

select *
from [oursystem].ourDB.dbo.tablefrom [oursystem].ourDB.dbo.table
join [linked_server_othersystem].developmentDB.dbo.tablename

select *
from [oursystem].ourDB.dbo.table
join [linked_server_othersystem].testDB.dbo.tablename ...

select *
from [oursystem].ourDB.dbo.table
join [linked_server_othersystem].UATDB.dbo.tablename ...

select *
from [oursystem].ourDB.dbo.table
join [linked_server_othersystem].LiveDB.dbo.tablename ...

Nice. One code set over multiple environments thereby preserving numerous audit and 'best practices' guidlines for source control. (And stopping our live system from accidentially linking to a UAT or test 'othersystem' database. )

Problem is, try as I might, the catalog seems to be REQUIRED when the provider is SQLNCLI. I.e. it must be specified in the TSQL code. Thus any attempt to use a default catalog (database) fails with the following error:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "test".

Linked server set up as follows:

EXEC master.dbo.sp_addlinkedserver @.server = N'LINKED_SERVER_OTHERSYSTEM', @.srvproduct=N'SQLNCLI', @.provider=N'SQLNCLI', @.datasrc=N'servername', @.catalog=N'testdatabase'

Is this bahaviour 'by design'?

Is there a workaround? (e.g. another provider to SQL Server 2005 that respects the catalog value)

Hotfix?

Any help - from someone who has actually done this (or not) much appreciated. And yes, I've read the doco and it suggests it should work and does not suggest it will not work.

Cheers,
Belgarion

Were you able to solve this problem? I have a similiar case with the same results.

Thanks,

Chris

|||

Hi Chris,

Alas no. I've concluded it's 'by design', and I might add, poorly documented as there appears to be nothing in the documentation that says this won't work.

Anybody tried this and suceeded? A chocolate fish awaits ...

Cheers,
Belgarion.