Working with linked servers in VSTS Database Pro can be a bit painful, especially when the linked server is a production server that should be (and typically is) inaccessible from developer's machines. If the appropriate linked server settings aren't set up on the design-time validation database server(which is the local database by default), the Database Pro project won't be able to build, which will prevent all sorts of useful things like schema compares for happening.
The solution to the problem is replicate the schema of the production database to another server (local is fine), and then to use sp_addlinkedserver to add the linked server using an alias rather than the actual server name, and then to use this alias in any stored procs that reference the linked server. The syntax for the call is:
EXEC sp_addlinkedserver @server='ServerAlias', @srvproduct='', @provider='SQLNCLI', @datasrc='ActualServerName'
Once sp_addlinkedserver has been called, the appropriate security settings still need to be set up, and this can be done from Management Studio or via sp_addlinkedsrvlogin.
By using an alias rather than the actual server name, the need for developers to have any login details for production systems on their machines is removed.
Posted
Oct 31 2007, 11:05 AM
by
nick