Sql-server – How to add a self-referencing linked server in SQL 2008

linked-serversql serversql-server-2005sql-server-2008

I am trying to replicate our live server set up local using a single SQL database.

In SQL 2005 I would added 2 linked servers both referencing itself with different names, each point to a different table.

How do I do this in SQL 2008. I've try the various providers and different parameters butto no avail.

The local server is using a trusted connection so I dont need any usernames/passwords

Im ideally need to set up the following linked references:

DBSVR1 – mydb_master
DBSVR2 – mydb_import

Any light on the subject would help, I managed to do it in 2005 about 8 months ago but cant remember how, now Im in 2008!

Best Answer

Well I managed to do it via script as that worked without issue rather than the GUI

EXEC sp_addlinkedserver @server='DBSVR2',

                                             @srvproduct='',

                                             @provider='SQLNCLI',

                                             @datasrc='.',--the data source

                                             @provstr='Integrated Security=SSPI';
Related Topic