Query Another Instance of SQL Server with sp_addlinkedserver and sp_addlinkedsrvlogin

Adding a linked server through SQL Server Management studio can be a tad confusing.   You could be much better off using a standard set of T-SQL commands to help expedite the setup using sp_addlinkedserver and sp_addlinkedsrvlogin.  Here goes …

SP_ADDLINKEDSERVER

The following example creates a linked server to another sql instance :

EXEC sp_addlinkedserver

@server=N'COMPUTER_A',     -- Enter Linked Server Name

@srvproduct=N'',                         -- Not Needed

@provider=N'SQLNCLI',           -- Enter the SQL Server Driver Here

@datasrc=N'COMPUTER_A\SQLEXPRESS'; -- Enter the Instance Name Here

SP_ADDLINKEDSRVLOGIN

The following example uses a mapping to make sure that all logins to the local server connect through to the linked server COMPUTER_A by using their own user credentials.

EXEC sp_addlinkedsrvlogin ' COMPUTER_A'

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server COMPUTER_A by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin ' COMPUTER_A', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

Once the linked server is in place you can use OPENQUERY or you can use a SYNONYM i.e.


USE tempdb;

GO

CREATE SYNONYM MySynonym FOR COMPUTER_A.AdventureWorks2012.HumanResources.Employee;

GO

References :

http://msdn.microsoft.com/en-us/library/ms190479(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms189811(v=sql.105).aspx

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s