Friday, July 29, 2011

Create a linked server to itself in MS SQL Server 2008

On my testing environment we have transaction and reporting databases on same server. But in live environment, they are on different servers and connect to each other using Linked Servers feature. So to reproduce this environment on test server, I needed to create a Linked Server on test "transaction" DB server that would "Link" to the "reporting" DB server on the same physical DB server.

This proved impossible using Management Studio; but I was able to successfully do this using t-sql code:

EXEC sp_addlinkedserver @server = N'reportingserver',
    @srvproduct = N' ',
    @provider = N'SQLNCLI',
    @datasrc = N'SQL_Server_Instance',
    @catalog = N'DB_name'

Hope this helps someone.

No comments: