We have a fairly sizable project at work and they are looking at using cross database queries in them. I'm not a fan of them, mostly on the Wookie's rationale of you can't just restore one database to get something working.
The scenario I'm struggling with is our database names change per environment. e.g. SLSDEVDB, SLSTESTDB, SLSLOADDB, SLSSTAGEDB, SLSPRODDB. In my simple mind, if I'm writing a cross database, henceforth abbreviated as xdb, query, it would take the form of "SELECT * FROM SLSDEVDB.dbo.Sales" in the development environment but when it got to production, it'd be "SELECT * FROM SLSPRODDB.dbo.Sales" We obviously don't want to change the database name per environment as someone would bone a reference and crash production. Architecture has suggested we use linked servers to overcome this challenge. By creating a link/alias to the sales database our code can be uniform across environments. "SELECT * FROM SLSDB.dbo.Sales" That's not a bad idea for once. Use linked servers to reference to the same box but allow us to alias the database.
The devil's in the details of course as I can't get that to work. I can get "SELECT SLSDB.WEBDEVDB.dbo.Sales" to work but that's aliasing the server, not the database name. Since their documentation basically ends with "and all these problems are solved with linked servers sp_addlinkedserver ..." perhaps there is a permutation I'm not seeing.
-- add a loopback linked server EXECUTE sp_addlinkedserver , @server='SLSDB' , @srvproduct='' , @provider='SQLNCLI' , @datasrc='localhost' , @catalog='SLSDEVDB' --An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB". SELECT top 1 * FROM SLSDB...Sales --Invalid object name 'SLSDB..Sales'. SELECT top 1 * FROM SLSDB..Sales --Invalid object name 'SLSDB.dbo.Sales'. SELECT top 1 * FROM SLSDB.dbo.Sales --Invalid object name 'SLSDB.Sales'. SELECT top 1 * FROM SLSDB.Sales -- This works but is useless as it does not address the changing database names SELECT top 1 * FROM SLSDB.SLSDEVDB.dbo.Sales --An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB". SELECT top 1 * FROM SLSDB..dbo.Sales -- An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "SLSDB". SELECT top 1 * FROM SLSDB.SLSDEVDB..Sales
Maybe it'll work if I create a user and login, default them to the specified catalog and add a linked server login but that seems like more hassle than it'll be worth. It's certainly not called out in their solution so I'm inclined to believe they never tested it.
Comments, solutions, etc always appreciated.