Microsoft® SQL Server® 2012 Bible

371

Chapter 15: Executing Distributed Queries

15

In a domain environment where users are connecting by using their domain credentials,

selecting the third option, “Be made using the login’s current security context” is the best

practice. If connected to the local server using Windows authentication, your Windows cre-

dentials will be used to connect to the remote server. However, if you are connecting to the

local server using SQL authentication, the login name and password will be used to connect

to the remote server.

Server Options Page

The Server Options page contains optional settings for changing the default characteristics

of the linked server, such as connection and query timeout, enabling or disabling distrib-

uted query access for the linked server, or enabling RPC to and from the linked server.

Linking to SQL Server via T-SQL

As you saw in the previous section, SQL Server Management Studio handles the connection

and login information in a single location. The other option is to establish the connection

via T-SQL and use separate T-SQL commands to handle the connection information and the

login information.

The process to establish a Linked Server via T-SQL is done through the

sp_addlinkedserver system stored procedure. If you connect to another SQL Server, the

syntax for using this stored procedure is simple: Pass two parameters, the server name and

server product:

EXEC sp_addlinkedserver @server='', @srvproduct='SQL Server'

This command doesn’t actually establish the link. It simply records information that SQL

Server can use to establish the link at a later time. Also this command doesn’t check to see

whether the server with the supplied name even exists or is accessible.

Now do a quick example in which two SQL Server instances exist; SQL Server 2012 and SQL

Server 2008 R2. The SQL Server 2008 R2 SQL Server has a database called AdventureWorks,

and this example will link the SQL Server 2008 R2 server to SQL Server 2012. Open a query

window on the SQL Server 2012 box and execute the following:

EXEC sp_addlinkedserver @server='AvalonDev', @srvproduct='SQL Server'

GO

SELECT * FROM AvalonDev.AdventureWorks.Person.Contact

The fi rst line of code creates a linked server to the development box. This is the same as

using the UI in SQL Server Management Studio to create a linked server. If I execute that

line of code individually and then look at my Linked Server node in Management Studio, I

would see a new linked server called AvalonDev in the list.

The second line executes a query against that link server. This query executes, but the

question is, will you get data back? The answer here is No. Why? Because the XML data

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

http://www.it-ebooks.info