Sunday, July 22, 2012

Adding a 'self' linked SQL Server (SQL Server R2)

To mimic a multi-SQL Server environment of one of our clients on a single server, I needed to link the SQL Server to itself. If found several suggestions by googling it, but only this one worked for me:

EXEC sp_addlinkedserver @server='alias', --the name of the linked server
                                             @srvproduct='',
                                             @provider='SQLNCLI',
                                             @datasrc='.',--the data source
                                             @provstr='Integrated Security=SSPI';

The other suggestions I found, used the same approach, but with a different provider. SQLNCLI worked for me.

No comments:

Post a Comment