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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>