I have queries in our database relying on linked servers. In the test environment I therefore have views / queries similar to
SELECT name FROM [TESTSERVER\TESTINSTANCE].testdb.dbo.employee
I am intending on using the database project from visual studio 2013/2015 & SVN.
I'd like to be able to use the schema compare to generate change scripts, but with substitution to
SELECT name FROM [LIVESERVER\LIVEINSTANCE].livedb.dbo.employee
I've used schema compare quite a lot, but not to make these changes.
Would I be along the right lines to assume I can set up variables something like
$env = [TESTSERVER\TESTINSTANCE].testdb
and then reference this in the project like:
SELECT name FROM $env.dbo.employee
so than on migration I just change $env=[TESTSERVER\TESTINSTANCE].livedb
Many thanks for your help.
asked Sep 02, 2015 at 02:52 PM in Default
This is precisely why I always recommend functionally-named linked servers rather than having the linked server names match the physical servers. If the linked server name is the same in dev and prod, you won't have this issue.
Regarding the use of project variables for this or anything else, you would reference it like this:
We do that in 1 situation where we have one database name in dev but a different one in integration and production.
As Dave suggested, synonyms can also help. I recommend using them with linked servers to make it easier to find all references to the linked server.
answered Sep 02, 2015 at 05:14 PM
Tom Staab ♦
Thanks Dave and Tom. A combination of the suggestions has got me much closer to what I wanted.
answered Sep 03, 2015 at 03:00 PM