2

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?

AstroCB
  • 12,337
  • 20
  • 57
  • 73
Lars Fosdal
  • 1,144
  • 8
  • 14

1 Answers1

1

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).

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thank you, Joe! Just the thing I needed! Voted up the issue. Strange they haven't implemented this already, as having multiple instances of identical databases on the same server - that each need to link tables to different external servers - can't be that uncommon? – Lars Fosdal May 12 '11 at 11:12