Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 692
Part V Data Connectivity
Best Practice
W
hile SQL Server can handle the technical problems of querying external data, if the two systems are
in fact two separate applications, then directly accessing an external data store will likely violate the
principle of encapsulation; and coupling the two data stores reduces the flexibility of the architecture. In
many IT shops this practice would not be approved. Instead, consider using a middle tier that communicates
with each of the two databases.
New in 2008
T
he syntax and feature set remain the same in SQL Server 2008, but the internal buffer space for distributed
queries has been increased for improved performance. Also, the transaction semantics of INSERT
EXECUTE against a loopback linked server have changed (from not supported to supported as long as MARS
is not enabled on the connection).
FIGURE 31-1
A linked server is a one-way direct connection and is not dependent on Management Studio registering
the servers. In this diagram, SQL Server instance A sees SQL Server instance B as a linked server, so A
can access B’s data.
Management Studio
Register
Servers
Login Information
Linked Server
Query
SQL Server
Instance B
SQL Server
Instance A
692
www.getcoolebook.com

Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 693
Executing Distributed Queries 31
A linked server can be a SQL server or any other data source with either an OLE DB provider or ODBC
drivers. Distributed queries can select data and modify it (
INSERT, UPDATE, DELETE), according to the
features of the OLE DB provider or ODBC driver.
SQL Server queries can reference external data by referring to the preconfigured linked server or specify-
ing the link in the query code.
In this chapter, I refer to the two data sources as
local
and
external
. Other descriptions of
distributed queries might refer to the same two servers as local and remote, or sending and
receiving.
In a sense, linking to an external data source only moves declaring the link from the query code to a
server administration task. Because queries can refer to the named link without concern for the loca-
tion or security particulars of the link, queries that use linked servers are more portable and easier to
maintain than queries that declare the external data source in the query code. If the database is moved
to a new server, then once the database administrator creates the appropriate links, the queries will work
without modification.
In the case of a distributed query, SQL Server is the client process receiving the results from the exter-
nal data source. Distributed queries can either pull the data into SQL Server for processing or pass the
query to the external data source for processing.
There’s more than one way to distribute data. You might want to consider replication
(see Chapter 36, ‘‘Replicating Data’’) or setting up a standby server as a reporting server
(see Chapter 46, ‘‘Log Shipping’’).
Accessing a Local SQL Server Database
When you access a second database on a single server, the same SQL Server engine processes the data.
Therefore, although the data is outside the local database, the query is not actually a distributed query.

A SQL Server query may access another database on the same server by referring to the remote stored
procedure, table, or view using the three parts of the four-part name. The complete four-part name is
the full address of the object:
Server.Database.Schema.Object
Because the database is on the same server, the server name is optional. Typically, the tables are in the
database owner schema (
dbo). If that’s the case, then dbo can be assumed:
USE CHA2;
SELECT LastName, FirstName
FROM OBXKites.dbo.Contact;
Result (abbreviated):
LastName FirstName

Adams Terri
693
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 694
Part V Data Connectivity
Andrews Ed

The schema can be assumed to be dbo and ignored by just leaving the schema empty. The following
query is functionally equivalent to the previous query, but does not specify the schema:
SELECT LastName, FirstName
FROM OBXKites Contact;
The code listings in this chapter are also in the ch31.sql script file. In addition, the Cape
Hatteras Adventures conversion script (
CHA2_Convert.sql) uses distributed queries exclu-
sively to convert the data from Access and Excel to SQL Server.
Linking to External Data Sources
SQL Server is also capable of establishing a link to any other data source that is ODBC- or OLE DB-

compatible. The link can be created using Management Studio or T-SQL code.
Linking to SQL Server with Management Studio
A link to another SQL Server can be established by means of Management Studio or code. Within
Management Studio’s Object Explorer, linked servers are listed under the Server Objects node. Selecting
Linked Servers ➪ context menu ➪ New Linked Server opens the New Server Properties form (see
Figure 31-2).
Selecting the server
In the General tab of the Linked Server Properties form, enter the name of the external SQL Server
in the Linked Server field, and click the SQL Server button in the Server Type section. To link to a
named instance of SQL Server, enter the instance name as server\instance without square brackets. In
Figure 31-2, the linked server is
MAUI\COPENHAGEN.
SQL Server 2008 can link to any other SQL Server 2000, 2005, or 2008 instance, or to a SQL Server
7 server, but SQL Server 2008 won’t link to a SQL Server 6.5 server without going through an
OBDC driver.
Configuring the logins
The whole point of linked servers is to enable local users to run queries that access data from other data
sources. If the external data source is SQL Server, then it will require some type of user authentication,
which is accomplished via mapping logins, or for those local users whose logins are not mapped, via
setting the default behavior.
The login map will either pass the user along without translating the login name if the Impersonate
option is checked, or translate any user’s login to a remote login and password if the Impersonate option
is not checked. Of course, on the external server, the login must be a valid login and must have been
granted security rights in order for the link to be effective.
694
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 695
Executing Distributed Queries 31
FIGURE 31-2
The Linked Server Properties form

The default connection options for a user not mapped are as follows:
■ Connection — Not be made: Restricts the ability to run distributed queries to those users
in the user mapping list. If a user not on the user mapping list attempts to run a distributed
query, then that user will receive the following error:
Server: Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied
because no login-mapping exists.
■ Connection — Be made without using a security context: This option is for non–SQL
Server external data sources and is not useful for SQL Server. SQL Server will attempt to
connect as the user
SQL without a password. If a user not on the user mapping list attempts
to run a distributed query, then that user will receive the following error:
OLE DB provider "SQLNCLI10" for linked server "MAUI\COPENHAGEN"
695
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 696
Part V Data Connectivity
returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "MAUI\COPENHAGEN"
reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"SQLNCLI10" for linked server "MAUI\COPENHAGEN".
For security purposes, connecting without using a security context is the default method
when creating the linked server using Management Studio. It won’t work with this method,
which is the intention. You must manually change it to a security context method that will
work — most likely the next method — using the login’s current security context.
■ Connection — Be made using the login’s current security context: When the local SQL
Server connects to the external SQL Server, it can delegate security, meaning that the local

SQL Server will connect to the external SQL Server using the local user’s login. Using this
method is similar to listing the user and selecting the Impersonate option except that this uses
security delegation, and to pass the security context, the login must be the exact same account,
not just the same login and password. If an account is deleted, creating a new account with
the same name and password isn’t sufficient.
The user’s rights and roles for the distributed query will be those assigned at the external
SQL Server.
To use security delegation, every server must run Windows 2000 or greater, and both
Kerberos and Active Directory must be enabled.
This is the default when creating the link using T-SQL code.
Best Practice
F
or most SQL Server–to–SQL Server distributed queries, the local login’s security context is the best
linked-server security option because it preserves the user’s identity and conforms to the SQL Server
security plan. If the infrastructure doesn’t support Kerberos and Active Directory, then map the users.
■ Connection — Be made using this security context: The final option simply assigns every
non-mapped local user to a hard-coded external SQL Server login. While this may be the
simplest method and might be useful when connecting to simpler data sources like Access or
Excel, it’s a risk because it allows every local user the same access to the external data source.
Be careful hard-coding security contexts; this method won’t pass a security audit, and it would
certainly exclude the external SQL Server from achieving C2-level security certification. There’s
no good reason to use this method when connecting to another SQL Server.
Configuring the options
The third tab in the Linked Server Properties form, Server Options, presents the following options,
which control how SQL Server expects to receive data from the external SQL Server:
■ Collation Compatible: Set this option to
true if the two servers, databases, columns, and
queries are using the same collation (character set and sort order).
696
www.getcoolebook.com

Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 697
Executing Distributed Queries 31
■ Data Access: If set to false, this option disables distributed queries to the external server.
■ RPC: If this option is set to
true, remote procedure calls may be made to the external server.
■ RPC Out: If this option is set to
true, remote procedure calls may be made from the external
server.
■ Use Remote Collation: If this option is set to
true, distributed queries will use the collation
of the external SQL Server, rather than that of the local server.
■ Collation Name: Specifies a collation for distributed queries. This option cannot be chosen if
collation compatibility is set.
■ Connection Timeout: The connection timeout in milliseconds. A value of 0 means to use the
default value set with
sp_configure.
■ Query Timeout: The distributed query timeout in milliseconds. A value of 0 means to use the
default value set with
sp_configure.
■ Publisher: The local server is a replication publisher.
■ Distributor: The local server is a replication distributor.
■ Lazy Schema Validation: When set, does not pre-validate the schema for distributed queries.
■ Enable Promotion of Distributed Transactions: Uses DTC for remote stored procedure
calls.
Once the link is properly established, a table listing will likely be available in the Catalogs ➪ Database
➪ Tables node under the linked server. The tables listed will be those of the login’s default database.
Deleting a linked server in Management Studio will also delete all security-login mappings.
Linking to S QL Server with T-SQL
Management Studio handles the connection and the login information in a single form. However, if you
choose to establish a linked server with T-SQL code, then the server connection and the login informa-

tion are handled by separate commands.
Establishing the link
To configure the server link with code, use the sp_addlinkedserver system stored procedure. If the
link is being made to another SQL Server, and the name of the other SQL Server instance is acceptable
as the name for the link, then only two parameters are required: the linked server name and the server
product. The following command creates a link to the
COPENHAGEN instance on my notebook, MAUI.
Note that
sp_addlinkedserver doesn’t actually establish the link: It merely records information SQL
Server can use to establish the link later. It doesn’t even try to check whether a server with the supplied
name exists and can be accessed.
Here’s an example:
EXEC sp_addlinkedserver
@server = ‘MAUI\COPENHAGEN’,
@srvproduct = ‘SQL Server’;
697
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 698
Part V Data Connectivity
If you run these scripts, then you’ll need to change the SQL Server instance names to
match your configuration.
To link to another SQL Server instance using a linked server name other than the SQL Server instance
name, two parameters are added. The
provider parameter must specify ‘SQLNCLI’,andthe
@datasrc (data source) parameter passes the actual SQL Server instance name of the linked server. The
@srvproduct (server product) parameter is left blank. The @server parameter will be the name by
which the linked server will be known. The example links to the
COPENHAGEN instance on MAUI,but
the linked server will be referred to as
Yonder in queries:

EXEC sp_addlinkedserver
@server = ‘Yonder’,
@datasrc = ‘MAUI\COPENHAGEN’,
@srvproduct = ‘’,
@provider=’SQLNCLI’;
The catalog view, sys.servers, lists the servers, including linked servers. The system
stored procedure,
sp_linkedservers, also returns information about linked servers:
SELECT [name], product, provider, data_source
FROM sys.servers
WHERE is_linked = 1;
To drop an existing linked server, which only severs the link and does not affect the external server, use
the
sp_dropserver system stored procedure:
EXEC sp_dropserver @server = ‘Yonder’;
If any login mappings exist for the linked server, they too will be dropped.
Distributed security and logins
In Management Studio, the security issue is broken down into two parts: login mapping and what to do
with non-mapped logins. T-SQL uses the
sp_addlinkedsrvlogin system stored procedure to handle
both parts, as follows:
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘rmtsrvname’,
@useself = ‘useself’, (default True)
@locallogin = ‘locallogin’, (default Null)
@rmtuser = ‘rmtuser’, (default Null)
@rmtpassword = ‘rmtpassword’ (default Null);
If the linked server was added using T-SQL instead of Management Studio, then the security option for
non-mapped logins is already configured to use the login’s current security context.
If the

@locallogin is null, then the setting applies to all non-mapped users. The @useself option
is the same as impersonate.
The following stored procedure call enables the
SQL2008VPC\Pn login to access the SQL2008VPC\
London
server as the sa user with the password P@s$w0rd:
698
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 699
Executing Distributed Queries 31
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘MAUI\COPENHAGEN’,
@useself = ‘FALSE’,
@locallogin = ‘MAUI\Pn’,
@rmtuser = ‘sa’,
@rmtpassword = ‘P@s$w0rd’;
The next example sets all non-mapped users to connect using their own security context (the recom-
mended option). The local user is
null, so this linked server login applies to all non-mapped users.
The
@useself option is not specified, so the default setting, true, will apply, causing the users to use
their local security context. This is the default setting, so you’ll only need this code if you want to return
to the default setting:
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘MAUI\COPENHAGEN’;
The third example will prevent all non-mapped users from executing distributed queries. The second
parameter,
@useself,issettofalse, and the mapping user login and password are left as null:
EXEC sp_addlinkedsrvlogin ‘SQL2008VPC\London’, ‘false’;
The catalog view, sys.linked_logins, lists the logins. The system stored procedure,

sp_helplinkedsrvlogin, also returns information about linked logins:
SELECT ls.[Name], dp.[Name]
FROM sys.servers ls
JOIN sys.linked_logins ll
ON ls.server_id = ll.server_id
JOIN sys.database_principals dp
ON ll.local_principal_id = dp.principal_id
WHERE Is_Linked = 1;
To drop a linked server login, use the sp_droplinkedsrvlogin system stored procedure:
EXEC sp_droplinkedsrvlogin
@rmtsrvname = ‘rmtsrvname’, (no default)
@locallogin = ‘locallogin’ (no default);
The following code example will remove the SQL2008VPC\Pn login that’s mapped to SQL2008VPC\
London
:
EXEC sp_droplinkedsrvlogin
@rmtsrvname = ‘SQL2008VPC\London’,
@locallogin = ‘SQL2008VPC\Pn’;
To remove the non-mapped user’s default mapping, run the same procedure but specify a null local
login, as follows:
EXEC sp_droplinkedsrvlogin ‘SQL2008VPC\London’, NULL;
699
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 700
Part V Data Connectivity
Linked server options
The linked server options shown in the Server Options tab of the Linked Server Properties form may be
set in code using the
sp_serveroption system stored procedure. The procedure must be called once
for each option setting:

EXEC sp_serveroption
@server = ‘server’,
@optname = ‘option_name’,
@optvalue = ‘option_value’;
The options are the same as those in the form (but in lowercase for case-sensitive collation, and in some
cases spelled slightly differently) with the addition of
lazy schema validation, which disables the
checking of the table schema for distributed queries. You may want to use
lazy schema validation
when you’re sure of the schema but want to reduce network overhead.
The catalog view, sys.servers, returns the linked server’s options. The system stored pro-
cedure,
sp_helpserver, also returns information about linked servers:
SELECT *
FROM sys.servers
WHERE Is_Linked = 1;
Linking with non–SQL Server data sources
If the external data source isn’t SQL Server, then SQL Server can likely still access the data. It depends
on the availability and the features of the ODBC drivers or OLE DB providers. SQL Server uses OLE
DB for external data, and several OLE DB providers are included with SQL Server. If for some reason
OLE DB isn’t available for the external data source, then use the ‘‘Microsoft OLE DB Provider for ODBC
Drivers’’ provider. Nearly every data-source type has an ODBC driver.
Besides the connection information listed in BOL in the sp_addlinkedserver (Transact-
SQL) page, a
great
list of connection strings for dozens of databases, to be used with the
ODBC data source, can be found at
www.connectionstrings.com.
To set up the linked server, either with code or via Management Studio, a data source (or location) and
possibly a provider string to supply additional information are required, in addition to the name of the

linked server, the provider name, and the product name. Some common data-source settings are listed
in Table 31-1.
Microsoft and Oracle don’t cooperate with each other when developing drivers. Therefore,
if connecting with Oracle is an important part of your application, then I recommend using
a high-performance third-party driver.
As two examples of linking to non–SQL Server data sources, the Cape Hatteras Adventures
sample database uses distributed queries to pull data from both Access and Excel. The sample database
models a typical small business that is currently using Access and Excel to store its customer list and
schedule.
700
www.getcoolebook.com
Nielsen c31.tex V4 - 07/21/2009 2:03pm Page 701
Executing Distributed Queries 31
TABLE 31-1
Other Common Data Source Settings
Link to OLE DB Provider Provider Name Data Source
ODBC data source Microsoft OLE DB
Provider for ODBC
MSDASQL System DSN of the
ODBC data source
(Alternately, Provider
string = ODBC
connection string)
MS Access Microsoft OLE DB
Provider for Jet
Microsoft.Jet.OLEDB.4.0 Database File Location
Excel Microsoft OLE DB
Provider for Jet
Microsoft.Jet.OLEDB.4.0 Spreadsheet File
Location (also req.

Provider string =
‘‘Excel 5.0’’
Oracle Microsoft OLE DB
Provider for Oracle
MSDAORA SQL*Net alias for
Oracle database
Oracle v8 + Oracle Provider for
OLE DB
OraOLEDB.Oracle Alias for the Oracle
database
As of this writing, Microsoft had still not yet released an x64-bit version of Microsoft.Jet
.OLEDB.4.0
, making working with distributed queries to Access or Excel difficult at best.
Linking to Excel
The code samples used in this section are taken directly from the CHA2_Convert.sql script, which
moves the data from the old version 1 (Access and Excel) to version 2 (SQL Server). The Cape Hatteras
Adventures folks have been keeping their tour schedule in Excel, as shown in Figure 31-3.
Within Excel, each spreadsheet page and named range appears as a table when accessed from an exter-
nal data provider. Within Excel, the named ranges are set up by means of the Insert ➪ Name ➪ Define
menu command. Excel’s Define Name dialog is used to create new named ranges and to edit the exist-
ing named ranges. The
CHA1_Schedule spreadsheet has five named ranges (as shown in Figure 31-4),
which overlap much like SQL Server views. Each of the five named ranges appears as a table when SQL
Server links to the spreadsheet. SQL Server will automatically pick up on column headers and use them
as column names. SQL Server can
SELECT, INSERT, UPDATE,andDELETE rows just as if this table
were a SQL Server table.
The following code sample sets up the Excel spreadsheet as a linked server:
EXEC sp_addlinkedserver
@server = ‘CHA1_Schedule’,

@provider = ‘Microsoft.Jet.OLEDB.4.0’,
701
www.getcoolebook.com