Microsoft® SQL Server® 2012 Bible

372

Part III: Advanced T-SQL Data Types and Querying Techniques

type is not supported in distributed queries. So, change the last line to the following and

re-execute the last line:

SELECT * FROM AvalonDev.AdventureWorks.HumanResources.Employee

Partial results of the query are shown here:

EmployeeID NationalIDNumber ContactID

---------- ---------------- ---------

1 14417807 1209

2 253022876 1030

You can also link to another SQL Server instance using a linked server name other than the

actual SQL Server instance name. To do this, simply add a couple of additional parameters:

the provider and datasrc parameters. When connecting to a SQL Server instance, the

provider value must be:

EXEC sp_addlinkedserver @server='ScottsDev',

@srvproduct='',

@provider='SQLNCLI',

@datasrc = 'AvalonDev'

GO

SELECT * FROM AvalonDev.AdventureWorks.HumanResources.Employee

Executing these lines of code produces the same results as the previous example, but you

also see another linked server called ScottsDev listed in the Linked Servers node in

Management Studio.

Dropping Linked Servers

You can drop linked servers via the sp_dropserver system stored procedure:

EXEC sp_dropserver @server='ScottsDev'

In addition, you can right-click the wanted linked server in SQL Server Management Studio

and select Delete from the context menu.

Distributed Security and Logins

In Management Studio, you can break down the security issue into two parts: login map-

ping and what to do with nonmapped logins. T-SQL uses the sp_addlinkedsrvlogin

system stored procedure to handle both, as follows:

EXEC sp_addlinkedsrvlogin

@rmtsrvname='rmtsrvname',

@useself='useself',

@locallogin='locallogin',

@rmtuser='rmtuser',

@rmtpassword='rmtpassword'

If the linked server were added using T-SQL instead of Management Studio, the security

option for nonmapped logins is already confi gured to use the login’s current security

c15.indd 372c15.indd 372 7/30/2012 4:50:39 PM7/30/2012 4:50:39 PM

http://www.it-ebooks.info