I have an MSSQL Server 2008 database "DBLive" that link to three different external servers [ExtServer1, ExtServer2, ExtServer3], all defined in sys.servers. There are several stored procedures that refer to stored procs or tables on the external servers, f.x. like 'SELECT TOP 1 @SomeId = Id FROM [ExtServer1].TheExtDB.dbo.SomeTable WHERE ...'.
Here is the challenge - if I want to put an database on the server - "DBTest" which is an identical copy of "DBLive" - but which should connect to different external servers - how do I make [ExtServer1 .. 3] point to a different external servers for "DBLive" and "DBTest"?
If this cannot be done - what would be the preferred way of linking external databases in such a way that two instances of the same db, can have their own external server references - without having differences in the stored procedures?
You'd probably want to use synonyms here. See the work around suggested in this Microsoft Connect issue (and vote up the issue while you're there).