Referencing external database in a View?

Question

In a project on which I am performing maintenance, I am referencing a database stored on another server. This reference was previously pointing at a database on the same server, but my testbed server isn't large enough to store both databases, so I'm having to refer back to the live database.

Anyway, whoever created this application made use of SQL Views, and I have found that when trying to edit the view, that it cannot reference an external database.

The existing reference is to myDatabase.dbo.tableName. When I try to change that to [MyServer.com].myDatabase.dbo.tableName moving away from the editor removes all of the square brackets, leaving me with MyServer.com.myDatabase.dbo.tableName which is not a proper table reference.

Any ideas what I can do to fix this?

Answer 1

It looks like you have added a linked server with the name "MyServer.com" I suggest you rename this to something without the dot, MyServerCom would be fine. Its ok if the details in the linked server point to a server that happens to be at that address.

Then the four part name can be MyServerCom.myDatabase.dbo.tableName

Answer 2

Don't edit the view definition in Enterprise Manager - use query analyser to script the view for ALTER, make the appropriate changes, and then run the script.

Enterprise manager does some fairly nasty things. I'd similarly recommend scripting an ALTER rather than using the "Design" option if you're using SSMS to manage your server/database.