Distributed Queries

Using any programming language, you can use ADO and OLE DB to connect to any data source. SQL Server 2000 uses OLE DB to give you access to any available data source.

You can write ad hoc queries in SQL Server to access external data using a rowset function in the FROM clause, as if it were a result set from a local table. All you need to use this functionality is an OLE DB provider and the properties required to establish a connection to the data.

For common queries, it is more efficient to define a linked server, declaring the connection properties permanently, so any user connected to the local server will have access to the remote server without specifying any connection property manually.

Ad Hoc Queries

SQL Server 2000 provides, two rowset functions to access heterogeneous data from any query:

• OPENDATASOURCE—To open any relational data source that exposes the data organized in catalogs, schemas, and data objects. SQL Server is a typical example, because it exposes data as DatabaseName.ObjectOwner.ObjectName, where the object can be a table, view, stored procedure, or user-defined function.

• OPENROWSET—To open any data source, relational or nonrelational, as long as you can connect to the data source through OLE DB.

Both rowset functions provide a similar functionality. The main difference is the way you invoke them, although the result is the same in both cases: a result set.

Using OPENDATASOURCE

Any database object in SQL Server can be identified by its fully qualified name: ServerName.CatalogName.SchemaName.ObjectName. In SQL Server, the CatalogName is the name of the database in which the object is stored. The SchemaName is usually the name of the owner of the object.

Working with ADO, from any programming language, you need to establish a connection to a server before trying to access its data. In Transact-SQL, use the OPENDATASOURCE function to connect to a server and retrieve data from there (see Listing 15.1).

Code Listing 15.1. Use OPENDATASOURCE to Connect to a Server and Retrieve Data

							
SELECT ProductID, ProductName
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQLBEInst3;User ID=sa;Password=;').Northwind.dbo.Products
WHERE UnitPrice > 50.0

ProductID   ProductName
----------- ----------------------------------------
9           Mishi Kobe Niku
18          Carnarvon Tigers
20          Sir Rodney's Marmalade
29          Thüringer Rostbratwurst
38          Côte de Blaye
51          Manjimup Dried Apples
59          Raclette Courdavault

As you saw in Listing 15.1, you use OPENDATASOURCE as a server name to fully qualify a table in a remote server. The OPENDATASOURCE function has two parameters:

• The OLE DB provider to use. In this example, you use the SQLOLEDB OLE DB provider to access a SQL Server 2000 server. You can use this provider to access any version of SQL Server.

• The connection string, required by the OLE DB provider, to connect to the data source.

Every OLE DB provider requires a different connection string. In this case, the connection string contains the following data:

• Data Source=SQLBEInst3;—In this case, it is the name of the SQL Server 2000 server you want to connect to, SQLBE, and the instance name Inst3. You can use the Server keyword instead of Data Source.

• User ID=sa;—This is the SQL Server login used to connect to the remote SQL Server. In this example, you connect to SQL Server using the sa account. You can use the UID keyword instead of User ID.

• Password=;—In this case, you provide a blank password. You can substitute the Password keyword with PWD.

The example in Listing 15.2 uses integrated security to connect to SQL Server through OPENDATASOURCE. As you can see in that example, the only difference is the inclusion of the Integrated Security=SSPI; string, or Trusted_Connection=yes;, instead of User ID=sa;Password=;.

Code Listing 15.2. You Can Use Integrated Security When Using OPENDATASOURCE to Connect to SQL Server

							
SELECT CustomerID, CompanyName
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBE;Integrated Security=SSPI;').Northwind.dbo.Customers
WHERE City = 'London'

CustomerID CompanyName
---------- ----------------------------------------
AROUT      Around the Horn
BSBEV      B's Beverages
CONSH      Consolidated Holdings
EASTC      Eastern Connection
NORTS      North/South
SEVES      Seven Seas Imports

In the examples from Listings 15.1 and 15.2, you might think that SQL Server connects to the remote servers, retrieves the data from the specified table, and locally applies the filter declared in the WHERE clause. However, the Query Processor is intelligent enough to detect that the WHERE clause applies exclusively to remote data and send the query to the remote server to be filtered remotely. In this way, the overall performance is improved, because the data is filtered where it belongs. Figure 15.1 shows the query plan of the query in Listing 15.2. In this query plan, you can see only one step, a remote query, defined as

SELECT Tbl1001."CustomerID" Col1003
,Tbl1001."CompanyName" Col1004
FROM "Northwind"."dbo"."Customers" Tbl1001
WHERE Tbl1001."City"=N'London'

You can use the OPENDATASOURCE function to join it to a local table (see Listing 15.3). In this case, the query plan is shown in Figure 15.2, where you can still see the remote query, with a merge join to connect the remote result set with the local result set.

Code Listing 15.3. You Can Join the Result of OPENDATASOURCE to a Local Table

							
SELECT OrderID, OrderDate, O.CustomerID, CompanyName
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBE;Integrated Security=SSPI;').Northwind.dbo.Customers
AS C
JOIN Northwind.dbo.Orders
AS O
ON O.CustomerID = C.CustomerID
WHERE City = 'London'
AND OrderDate BETWEEN '1996-12-01'
AND '1996-12-15'
OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------

10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

If you modify the example from Listing 15.3 to retrieve the Customer information from the SQLBE server and the Orders information from the SQLBEInst2 server, the query should be as in Listing 15.4, and the query plan is shown in Figure 15.3.

Code Listing 15.4. Use OPENDATASOURCE to Connect to a Server and Retrieve Data

							
SELECT OrderID, OrderDate, O.CustomerID, CompanyName
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBE;Integrated Security=SSPI;').Northwind.dbo.Customers
AS C
JOIN OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBEInst2;Integrated Security=SSPI;').Northwind.dbo.Orders
AS O
ON O.CustomerID = C.CustomerID
WHERE City = 'London'
AND OrderDate BETWEEN '1996-12-01'
AND '1996-12-15'

OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------
10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

You can use OPENDATASOURCE to retrieve data from an Access database, as the DTS.MDB database defined in Chapter 14, in the "Export a SQL Server Table to an Access Database" section. Listing 15.5 shows how to use the Microsoft Jet OLE DB provider to connect to an Access database, and Figure 15.4 shows its query plan.

Code Listing 15.5. Use OPENDATASOURCE to Connect to a Remote SQL Server and an Access Database

							
SELECT OrderID, OrderDate--, O.CustomerID, CompanyName
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBE;Integrated Security=SSPI;').Northwind.dbo.Customers
AS C
JOIN OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:SQLDTS.MDB";User ID=Admin;Password=;')...Orders
AS O
ON O.CustomerID = C.CustomerID
WHERE City = 'London'
AND OrderDate BETWEEN '1996-12-01'
AND '1996-12-15'

OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------
10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

If you compare the query plan from Figure 15.4 to the one from Figure 15.3, you can see two extra steps after retrieving the Orders data from Access:

• A filter to select the data range.

• A sort operation to be able to execute the merge join.

These extra steps are required because SQL Server cannot ask the Jet OLE DB provider to execute these tasks remotely.

You can use OPENDATASOURCE to modify a table in an UPDATE, DELETE, or INSERT statement, as in Listing 15.6.

Code Listing 15.6. Use OPENDATASOURCE to Specify a Source for UPDATE, INSERT, or DELETE Statements

							
PRINT 'Before moving BSBEV from London'+ CHAR(10)

SELECT CustomerID, CompanyName, City
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBEInst3;Integrated Security=SSPI;').Northwind.dbo.Customers
WHERE CustomerID = 'BSBEV'

PRINT CHAR(10) + 'Moving BSBEV from London to Southampton'+ CHAR(10)

UPDATE OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBEInst3;Integrated Security=SSPI;').Northwind.dbo.Customers
SET City = 'Southampton'
WHERE CustomerID = 'BSBEV'

PRINT CHAR(10) + 'After BSBEV moved from London to Southampton'+ CHAR(10)

SELECT CustomerID, CompanyName, City
FROM OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBEInst3;Integrated Security=SSPI;').Northwind.dbo.Customers
WHERE CustomerID = 'BSBEV'

PRINT CHAR(10) + 'Moving BSBEV back to London'+ CHAR(10)

UPDATE OPENDATASOURCE(
'SQLOLEDB',
'Server=SQLBEInst3;Integrated Security=SSPI;').Northwind.dbo.Customers
SET City = 'London'
WHERE CustomerID = 'BSBEV'
Before moving BSBEV from London

CustomerID CompanyName                              City
---------- ---------------------------------------- ---------------
BSBEV      B's Beverages                            London

(1 row(s) affected)

Moving BSBEV from London to Southampton

(1 row(s) affected)

After BSBEV moved from London to Southampton

CustomerID CompanyName                              City
---------- ---------------------------------------- ---------------
BSBEV      B's Beverages                            Southampton

(1 row(s) affected)

Moving BSBEV back to London

(1 row(s) affected)
					

Using OPENROWSET

You can use OPENROWSET to retrieve result sets from any data source, in a way similar to OPENDATASOURCE. The main difference is that you can send the query to the data source, using the syntax that the OLE DB provider accepts, and the OLE DB provider will return the requested result set.

Listing 15.7 shows how to use OPENROWSET to retrieve data from an instance of SQL Server, in a way similar to that seen earlier in Listing 15.1. How ever, you can send the entire query, including the WHERE clause, directly to the OPENROWSET function, as shown in the second example of Listing 15.7.

Code Listing 15.7. Use OPENROWSET to Connect to a Server and Retrieve Data

							
SELECT ProductID, ProductName
FROM OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst3;UID=sa;PWD=;', Northwind.dbo.Products)
WHERE UnitPrice > 50.0

SELECT *
FROM OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst3;UID=sa;PWD=;',
'SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice > 50.0')

ProductID   ProductName
----------- ----------------------------------------
9           Mishi Kobe Niku
18          Carnarvon Tigers
20          Sir Rodney's Marmalade
29          Thüringer Rostbratwurst
38          Côte de Blaye
51          Manjimup Dried Apples
59          Raclette Courdavault

The query plans of the two queries shown in Listing 15.7 are not the same. In Figure 15.5, you can see a remote query step for the first query and a remote scan for the second query; however, the results are the same, and in both cases the query is sent to the remote server to be executed there.

You can join the result set returned by OPENROWSET to other result sets. Listing 15.8 shows an example similar to Listing 15.4, but in this case you retrieve the same result in three different ways:

• The first query joins two OPENROWSET functions against two servers (SQLBEInst2 and SQLBEInst3).

• The second query joins two OPENROWSET functions against the same server (SQLBEInst2).

• The third query uses OPENROWSET just once to retrieve the entire result set from a single server (SQLBEInst2).

Code Listing 15.8. Use OPENROWSET to Retrieve a Result Set to JOIN to Other Result Sets

							
PRINT 'Using OPENROWSET twice againt two servers'+ CHAR(10)

SELECT OrderID, OrderDate, O.CustomerID, CompanyName
FROM OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst2;Trusted_Connection=yes;',
Northwind.dbo.Customers)
AS C
JOIN OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst3;Trusted_Connection=yes;',
Northwind.dbo.Orders)
AS O
ON O.CustomerID = C.CustomerID
WHERE City = 'London'
AND OrderDate BETWEEN '1996-12-01'
AND '1996-12-15'

PRINT 'Using OPENROWSET twice againt one server'+ CHAR(10)

SELECT OrderID, OrderDate, O.CustomerID, CompanyName
FROM OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst2;Trusted_Connection=yes;',
Northwind.dbo.Customers)
AS C
JOIN OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst2;Trusted_Connection=yes;',
Northwind.dbo.Orders)
AS O
ON O.CustomerID = C.CustomerID
WHERE City = 'London'
AND OrderDate BETWEEN '1996-12-01'
AND '1996-12-15'

PRINT 'Using OPENROWSET once'+ CHAR(10)

SELECT *
FROM OPENROWSET(
'SQLOLEDB',
'Server=SQLBEInst2;Trusted_Connection=yes;',
'SELECT OrderID, OrderDate, O.CustomerID, CompanyName
FROM Northwind.dbo.Customers AS C
JOIN
Northwind.dbo.Orders AS O
ON O.CustomerID = C.CustomerID
WHERE City = ''London''
AND OrderDate BETWEEN ''1996-12-01''
AND ''1996-12-15''')
Using OPENROWSET twice againt two servers

OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------
10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

Using OPENROWSET twice againt one server

OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------
10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

Using OPENROWSET once

OrderID  OrderDate                CustomerID CompanyName
-------- ------------------------ ---------- --------------------
10377    1996-12-09 00:00:00.000  SEVES      Seven Seas Imports

Figure 15.6 shows the query plan produced to execute the first query from Listing 15.8. You can see that this query plan is similar to the query plan shown in Figure 15.3.

Figure 15.7 shows the query plan produced when the second query from Listing 15.8 is executed. Because both OPENROWSET functions connect to the same server, only one remote query step is required to retrieve the final result set. This is more efficient than returning two complete result sets and joining them locally. The Query Processor makes this decision automatically, and through this way you can save network bandwidth because only the requested data is transferred from the remote server.

Finally, you can use OPENROWSET to send a complex query to a remote SQL Server, as seen in the third example in Listing 15.8. Figure 15.8 shows the query plan produced to execute this query. This query contains only one OPENROWSET function, whereas the query plan shows a single remote scan step.

In the examples in this section, you used the same kind of connection string as in the OPENDATASOURCE function. You can use a different syntax in OPENROWSET to specify the server name, the user ID, and the password, as illustrated in Listing 15.9.

Code Listing 15.9. You Can Use a Simplified Syntax in OPENROWSET to Connect to a Data Source

							
PRINT 'From SQL Server'+ CHAR(10)

SELECT CategoryID, CategoryName
FROM OPENROWSET(
'SQLOLEDB',
'SQLBEInst3';'sa';'', Northwind.dbo.Categories)

PRINT 'From Access'+ CHAR(10)

SELECT CategoryID, CategoryName
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'D:SQLDTS.MDB';'Admin';'',
'SELECT * FROM Categories
ORDER BY CategoryName DESC')
From SQL Server

CategoryID  CategoryName
----------- ---------------
1           Beverages
2           Condiments
3           Confections
4           Dairy Products
5           Grains/Cereals
6           Meat/Poultry
7           Produce
8           Seafood

From Access

CategoryID  CategoryName
----------- ---------------
8           Seafood
7           Produce
6           Meat/Poultry
5           Grains/Cereals
4           Dairy Products
3           Confections
2           Condiments
1           Beverages
					

You can use OPENROWSET, with the OLE DB provider for ODBC, to connect to any Data Source Name (DSN) defined in the ODBC Manager. Listing 15.10 uses the LocalServer system DSN, which points to the default SQL Server instance. You can use this provider to establish DSN-less connections to any data source also, specifying the ODBC driver to use as seen in the second example in Listing 15.10 .

Code Listing 15.10. You Can Use the OLE DB Provider for ODBC with OPENROWSET to Access Any ODBC Data Source Name

							
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=LocalServer',
'SELECT * FROM Northwind.dbo.Region')

RegionID    RegionDescription
----------- --------------------------------------------------
1           Eastern
2           Western
3           Northern
4           Southern
					

Code Listing 15.11. Use OPENROWSET to Work with Transact-SQL Administrative Statements As Result Sets

							
SELECT spid, cmd, dbname
FROM OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
'EXEC sp_who')
WHERE dbname = 'Northwind'

spid   cmd              dbname
------ ---------------- -----------------
62     SELECT           Northwind
63     AWAITING COMMAND Northwind
65     AWAITING COMMAND Northwind

You can retrieve results from remote stored procedures or user-defined functions by using OPENROWSET, as illustrated in Listing 15.12.

Code Listing 15.12. Use OPENROWSET to Open Remote User-Defined Functions

							
SELECT OrderID, CustomerID, TotalValue, ShipCountry, OrderDate
FROM OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
'Select * FROM Northwind.dbo.TopTenOrders()')

OrderID   CustomerID TotalValue      ShipCountry     OrderDate
--------- ---------- --------------- --------------- ------------------------
10865     QUICK      16387.5000      Germany         1998-02-02 00:00:00.000
10981     HANAR      15810.0000      Brazil          1998-03-27 00:00:00.000
11030     SAVEA      12615.0500      USA             1998-04-17 00:00:00.000
10889     RATTC      11380.0000      USA             1998-02-16 00:00:00.000
10417     SIMOB      11188.4000      Denmark         1997-01-16 00:00:00.000
10817     KOENE      10952.8450      Germany         1998-01-06 00:00:00.000
10897     HUNGO      10835.2400      Ireland         1998-02-19 00:00:00.000
10479     RATTC      10495.6000      USA             1997-03-19 00:00:00.000
10540     QUICK      10191.7000      Germany         1997-05-19 00:00:00.000
10691     QUICK      10164.8000      Germany         1997-10-03 00:00:00.000

If you want to update data through the OPENROWSET function, you must use the OPENROWSET function in the UPDATE clause as if it were a table. Listing 15.13 shows an example of UPDATE, INSERT, and DELETE statements using OPENROWSET .

Code Listing 15.13. You Can UPDATE, INSERT, and DELETE a Result Set Returned by OPENROWSET

							
DECLARE @ID int

PRINT 'Insert a new Category'+ CHAR(10)

INSERT OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
Northwind.dbo.Categories)
(CategoryName)
VALUES ('New Category')

PRINT 'Retrieve the CategoryID'+ CHAR(10)

SELECT @ID = CategoryID
FROM OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
'SELECT CategoryID
FROM Northwind.dbo.Categories
WHERE CategoryName = ''New Category''')

PRINT 'Update the name of the new Category'+ CHAR(10)

UPDATE OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
Northwind.dbo.Categories)
SET CategoryName = 'Other'
WHERE CategoryID = @ID

PRINT 'Delete the new Category'+ CHAR(10)

DELETE OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
Northwind.dbo.Categories)
WHERE CategoryID = @ID
Insert a new Category

(1 row(s) affected)

Retrieve the CategoryID

Update the name of the new Category

(1 row(s) affected)

Delete the new Category

(1 row(s) affected)
					

You can use OPENROWSET to create a more accurate version of the scalar user-defined function dbo.Today(), created in Listing 10.4, because using OPENROWSET you can actually reconnect to SQL Server. Almost any instruction is available in OPENROWSET, including not valid built-in functions in user-defined functions, such as GetDate. Listing 15.14 shows how to implement the Today() function using OPENROWSET.

Code Listing 15.14. You Can Use OPENROWSET Inside User-Defined Functions

							
USE Northwind
GO

IF OBJECT_ID('Today', 'FN') IS NOT NULL
DROP FUNCTION dbo.Today
GO
-- Returns the actual system date
-- obtained by using OPENROWSET as a callback function

CREATE FUNCTION dbo.Today
()
RETURNS smalldatetime
AS
BEGIN
DECLARE @sdt smalldatetime

SELECT @SDT = CONVERT(varchar(10), TodaysDate, 120)
FROM OPENROWSET('SQLOLEDB',
'Server=SQLBE;Trusted_Connection=yes;',
'SELECT Getdate() AS TodaysDate')
RETURN @SDT

END
GO

SELECT dbo.Today()
AS Today

Today
------------------------------------------------------
2001-01-02 00:00:00
					

Linked Servers

Any client application can establish connections to more than one server at a time, but it is not possible to join directly result sets from different connections.

Using the rowset functions from the last section, you can execute queries that relate information coming from different data sources. However, SQL Server must establish a connection on every call to OPENDATASOURCE or OPENROWSET, using the connection string or connection parameters sent along with the function call.

If you are a Microsoft Access user, you will be familiar with the concept of a linked table. This is a permanent definition of a logical connection to an external data source.

SQL Server 2000 implements links to any OLE DB data source, as linked servers, to any SQL Server instance. Any user connected to an instance of SQL Server can access any linked server defined in that instance without knowing the parameters to connect to this particular data source. In this way, you have the flexibility of the OPENROWSET and OPENDATASOURCE functions without exposing to the users the complexity inherent to any OLE DB connection.

Users can access objects on linked servers, using fully qualified four-part names and using any data access statement. In this way, you can use any kind of information exposed by the OLE DB provider as if it were a table on a database, and join that information to other tables in the local server.

In the following sections, you will learn how to set up and use linked servers.

Setting Up and Querying Linked Servers

The first thing you need to set up a linked server, which connects to an external data source, is an appropriate OLE DB provider. Microsoft has tested the following OLE DB providers to use in a linked server:

• Microsoft OLE DB Provider for SQL Server (SQLOLEDB)—Use this provider to connect to SQL Server 6.5, 7.0, and 2000.

• Microsoft OLE DB Provider for ODBC (MSDASQL)—Use this provider to connect to any data source, as long as you have a valid ODBC driver for this particular data source.

• Microsoft OLE DB Provider for Jet (Microsoft.Jet.OLEDB.4.0)—This provider connects you to Microsoft Access databases, Microsoft Excel spreadsheets, and text files.

• Microsoft OLE DB Provider for DTS Packages (DTSPackageDSO)—This provider gives you access to the result set of a transformation step from a DTS package.

• Microsoft OLE DB Provider for Oracle (MSDAORA).

• Microsoft OLE DB Provider for Microsoft Directory Services (ADSDSOObject)—Use this provider to get information from the Active Directory information on Microsoft Windows 2000 or Microsoft Exchange 2000.

• Microsoft OLE DB Provider for Microsoft Indexing Service (MSIDXS)—This provider gives you access to local files indexed by the Microsoft Indexing Service.

• Microsoft OLE DB Provider for DB2 (DB2OLEDB)—This provider is part of the Microsoft Host Integration Server, and gives you connectivity to IBM DB2 databases.

To set up a linked server, you can use the sp_addlinkedserver system stored procedure. Listing 15.15 shows how to create a linked server in the SQLBE server to connect to the SQLBEInst2 instance of SQL Server .

Code Listing 15.15. Setting Up a Linked Server Using the sp_addlinkedserver System Stored Procedure

							
-- Use sp_addlinkedserver with
-- SQL Server as a product name

EXEC sp_addlinkedserver
@server = N'SQLBEInst3',
@srvproduct = N'SQL Server'
GO

-- Use sp_addlinkedserver with
-- SQLOLEDB as a provider name

EXEC sp_addlinkedserver
@server = N'SQLBEInst2',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = N'SQLBEInst2'
GO

-- Use sp_addlinkedserver with
-- SQLOLEDB as a provider name
-- and with an initial catalog

EXEC sp_addlinkedserver
@server = N'NewSQLBEInst2',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = N'SQLBEInst2',
@catalog = N'Northwind'
GO

To execute the sp_addlinkedserver system stored procedure to create a linked server to a SQL Server instance, you must supply

• The actual name of the SQL Server default instance or named instance (@server)

• N'SQL Server' as product name (@srvproduct)

or

• The logical name you want to provide to the linked server (@server).

• N'' as product name (@srvproduct).

• The name of the OLE DB provider used to connect to the data source—in this case, N'SQLOLEDB' (@provider).

• The actual name of the SQL Server default instance or named instance to connect (@datasrc).

• Optionally, you can specify the catalog or database to which to connect (@catalog). However, this parameter is used only to specify an initial database to connect. After the connection is made, you can access any database on that server, providing you have permissions to use it and the @catalog parameter is disregarded.

To query a linked server, you must use a fully qualified name, using four-part names, as seen in Listing 15.16. In this way, tables from linked servers can be used as any local table on any DML operation, such as SELECT, INSERT, UPDATE, or DELETE. The last example on Listing 15.16 shows how to link remote tables to other remote and local tables, as if all these tables were local tables .

Code Listing 15.16. You Can Use LinkedServers to Access Remote Tables Using Fully Qualified Names

							
PRINT 'Selecting data from a linked server'
+ CHAR(10)

SELECT CategoryID, CategoryName
FROM [SQLBEInst3].northwind.dbo.categories
WHERE CategoryID BETWEEN 1 AND 3

PRINT 'Inserting a row into a linked server'
+ CHAR(10)

INSERT SQLBEInst2.Northwind.dbo.Categories
(CategoryName)
VALUES('More products')

PRINT 'Updating a row from a linked server'
+ CHAR(10)

UPDATE SQLBEInst2.Northwind.dbo.Categories
SET CategoryName = 'Extra Products'
WHERE CategoryName = 'More products'

PRINT 'Deleting a row from a linked server'
+ CHAR(10)

DELETE NewSQLBEInst2.Northwind.dbo.Categories
WHERE CategoryName = 'Extra Products'


PRINT 'Join data coming from linked servers'
+ CHAR(10)

SELECT OrderDate, Quantity, OD.UnitPrice,
CategoryName, ProductName
FROM [SQLBEInst3].northwind.dbo.categories C
JOIN SQLBEInst2.Northwind.dbo.Products P
ON P.CategoryID = C.CategoryID
JOIN NewSQLBEInst2.Northwind.dbo.[Order Details] OD
ON OD.ProductID = P.ProductID
JOIN Northwind.dbo.Orders O
ON O.OrderID = OD.OrderID
WHERE P.CategoryID = 1
AND Year(OrderDate ) = 1998
AND CustomerID = 'BSBEV'
Selecting data from a linked server

CategoryID  CategoryName
----------- ---------------
1           Beverages
2           Condiments
3           Confections

(3 row(s) affected)

Inserting a row into a linked server

(1 row(s) affected)

Updating a row from a linked server

(1 row(s) affected)

Deleting a row from a linked server

(1 row(s) affected)

Join data coming from linked servers

OrderDate               Quantity UnitPrice   CategoryName    ProductName
----------------------- -------- ----------- --------------- -----------
1998-04-14 00:00:00.000 30       46.0000     Beverages       Ipoh Coffee

(1 row(s) affected)
					

If you want to execute a stored procedure from a linked server, you must first enable RPC (Remote Procedure Calls) on the linked server. Listing 15.17 shows an example of how to enable RPC in a linked server by using the sp_serveroption system stored procedure, and how to call a stored procedure remotely.

Code Listing 15.17. You Can Execute Remote Stored Procedures in a Linked Server

							
-- Set RPC OUT true
-- to accept remote procedure calls

EXECUTE sp_serveroption N'SQLBEinst2', 'RPC OUT', 'true'

-- Execute a remote procedure

EXECUTE sqlbeinst2.northwind.dbo.CustOrderHist 'BSBEV'

ProductName                              Total
---------------------------------------- -----------
Aniseed Syrup                            30
Boston Crab Meat                         10
Geitost                                  15
Gnocchi di nonna Alice                   20
Gustaf's Knäckebröd                      21
Ipoh Coffee                              30
Konbu                                    23
Manjimup Dried Apples                    3
Maxilaku                                 6
Mozzarella di Giovanni                   1
Outback Lager                            7
Raclette Courdavault                     4
Ravioli Angelo                           6
Sir Rodney's Scones                      29
Spegesild                                15
Steeleye Stout                           20
Tarte au sucre                           10
Uncle Bob's Organic Dried Pears          34
Wimmers gute Semmelknödel                9

(19 row(s) affected)

You can define a linked server to connect to an Access database. Listing 15.18 shows how to create a linked server to connect to the DTS.MDB Access database created in Chapter 14. In this example, you must write in @datasrc the location of the MDB file .

Code Listing 15.18. Setting Up a Linked Server to an Access Database

							
EXEC sp_addlinkedserver
@server = N'DTSMDB',
@srvproduct = N'Access 2000',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'd:sqldts.mdb'
GO

-- Map every login in SQL Server
-- to the Admin login in Access

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'DTSMDB',
@useself= false,
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = NULL
GO

-- Read data from Access
-- through the linked server

SELECT ProductName, UnitPrice
FROM DTSMDB...Products
WHERE UnitPrice > 100
GO

ProductName                              UnitPrice
---------------------------------------- ---------------------
Thüringer Rostbratwurst                   123.7900
Côte de Blaye                            263.5000
					

As you saw in Listing 15.18, it is not enough to create the linked server to access its data. In some cases, it is necessary to map the local logins to remote logins to be able to connect to the linked server. To map logins, use the sp_addlinkedsrvlogin system stored procedure. This procedure accepts the following parameters:

• @rmtsrvname—The name of the linked server. In this case, it is 'DTSMDB'.

• @useself— True to map every local account to the same account in the linked server, so the @locallogin, @rmtuser, and @rmtpassword parameters will be ignored. In this case, you don't want to automatically map every local user to a remote user, because your Access database is not secured in this case, so you give a value of @rmtuser = false.

• @locallogin—Name of the local login to map, only if @useself = false. You specify NULL in this case because you want to map all local logins to the same remote login.

• @rmtuser—Name of the remote login to map the @locallogin. If you use an unsecured Access database, @rmtuser = 'Admin'.

• @rmtpassword—Password to use in the remote server for the remote user specified in @rmtuser. In this case, it must be a blank password, @rmtpassword = NULL.

You can create a linked server to read text files from a directory. To test it, you can create a little text file, like the one in Listing 15.19, in the D:SQL directory.

Code Listing 15.19. Ages.txt File

							
ID    Age    Name
1    55    Joseph
2    32    John
3    34    Frederick
4    70    Antony
5    65    Francis
6    75    Jon
7    43    Manfred
8    21    Dick
9    18    Louis

Now, you can create a linked server to read files in any directory, as in the example in Listing 15.20, using the OLE DB provider for Jet.

Code Listing 15.20. Setting Up a Linked Server to a Disk Directory

							
-- Create a Linked Server
-- To read text files from
-- the D:SQL directory

EXEC sp_addlinkedserver
@server = N'TextFiles',
@srvproduct = N'Text files',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'D:SQL',
@provstr='Text'

GO

-- Map every login in SQL Server
-- to the Admin login in Jet

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'TextFiles',
@useself= false,
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = NULL
GO

-- Read data from the Ages.txt file

SELECT *
FROM TextFiles...ages#txt
GO

ID_Age_Name
-----------------------------------
1    55    Joseph
2    32    John
3    34    Frederick
4    70    Antony
5    65    Francis
6    75    Jon
7    43    Manfred
8    21    Dick
9    18    Louis
					

Pass-Through Queries

When working with linked servers, SQL Server 2000 always tries to send the queries to the linked servers to be processed remotely. This decreases the network traffic. In particular, the query execution is more efficient because it is performed in the same server in which the affected data is stored. In this case, the query is "passed through" the linked server for remote execution.

You can force the execution of pass-through queries remotely by using the OPENQUERY function. OPENQUERY is similar to the OPENDATASOURCE and OPENROWSET functions, because it connects to a remote data source and returns a result set. However, OPENQUERY uses a linked server definition to connect to the remote server. In this way, you have a persistent definition of the connection properties, providing easier maintenance of your database application.

As you can see in the examples from Listing 15.21, the syntax of OPENQUERY is very simple: You provide the linked server name to send the query and the query to be executed.

Code Listing 15.21. Using OPENQUERY to Send Pass-Through Queries to a Linked Server

							
-- Gets the date and time in the linked server

SELECT *
FROM OPENQUERY(SQLBEinst2,
'SELECT Getdate() AS Now')

-- Reads some data from the linked server

SELECT DISTINCT ProductName, UnitPrice
FROM OPENQUERY(SQLBEinst2,
'SELECT DISTINCT P.ProductID, ProductName,
OD.UnitPrice
FROM Northwind.dbo.Products P
JOIN Northwind.dbo.[Order details] OD
ON OD.ProductID = P.ProductID
WHERE OD.UnitPrice > 100')

-- Updating data through OPENQUERY

UPDATE OPENQUERY(SQLBEinst2,
'SELECT *
FROM Northwind.dbo.Categories')
SET CategoryName = 'Obsolete'
WHERE CategoryID = 3

-- Testing changes

SELECT categoryname
FROM SQLBEInst2.Northwind.dbo.categories
WHERE CategoryID = 3
GO

Now
------------------------------------------------------
2001-02-20 17:33:16.370

ProductID   ProductName                              UnitPrice
----------- ---------------------------------------- ---------------------
29          Thüringer Rostbratwurst                   123.7900
38          Côte de Blaye                            210.8000
38          Côte de Blaye                            263.5000

categoryname
---------------
Obsolete

The first query in Listing 15.21 retrieves the system data and time from the linked server.

The second query remotely executes a query that joins two tables. When the combined result set is returned, the local server performs the DISTINCT operation.

The third query updates data remotely using the OPENQUERY function.

Partitioned Views

Consider you have a very big table, SalesInfo, with your worldwide sales information. You have different regions and you want to be able to execute queries to any subset of the complete sales table, regardless of the region.

This table is too big and the maintenance is starting to be difficult. You decide to divide the table among four servers, North, West, South, and East, each one storing data from only one region.

To ensure that you store on every server only data related to that specific server, create a check constraint that enforces the value for the particular regions this server manages.

Now you want to access any data from anywhere, so, on every server you create a view that combines the data from every server with the data from the other servers by a UNION ALL. Use UNION ALL because you do not want to remove duplicates in the final result set. This view is called a partitioned view.

You can test a simple version of this technique using the example from Listing 15.22. This script can be run in a single server and single instance, and still it uses the partitioned view technique. This is the only simplification used in this example. You can change this script to create every table in a different instance or server and modify the view to retrieve every table from the appropriate server, as shown in Listing 15.22.

Code Listing 15.22. Create a Partitioned View Based on Four Tables

							
USE Northwind
GO

-- Create the partitioned table
-- RegionID is the partitioning column
-- it is part of the PRIMARY KEY
-- and it has a check constraint
-- to delimit ranges per table

-- RegionID = 3 North

CREATE TABLE SalesInfoNorth (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 3),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_North
PRIMARY KEY (OrderID, RegionID))


-- RegionID = 4 South

CREATE TABLE SalesInfoSouth (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 4),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_South
PRIMARY KEY (OrderID, RegionID))

-- RegionID = 1 East

CREATE TABLE SalesInfoEast (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 1),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_East
PRIMARY KEY (OrderID, RegionID))

-- RegionID = 2 West

CREATE TABLE SalesInfoWest (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 2),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_West
PRIMARY KEY (OrderID, RegionID))
GO

-- Create a View that gets the entire
-- SalesInfo informations
-- Note the use of UNION ALL
-- This is the Partitioned View

CREATE VIEW SalesInfo
AS
SELECT *
FROM SalesInfoNorth
UNION ALL
SELECT *
FROM SalesInfoSouth
UNION ALL
SELECT *
FROM SalesInfoEast
UNION ALL
SELECT *
FROM SalesInfoWest
GO

-- Populate the partitioned tables
-- using the SalesInfo view directly
-- The partitioned view mechanism
-- will send every row to the appropriate
-- destination table automatically.

INSERT SalesInfo
SELECT o.OrderID,
T.RegionID,
O.OrderDate,
sum(UnitPrice * Quantity * (1-Discount)),
O.EmployeeID
FROM Orders O
JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
JOIN EmployeeTerritories ET
ON ET.EmployeeID = O.EmployeeID
JOIN Territories T
ON T.TerritoryID = ET.TerritoryID
GROUP BY O.OrderID, T.RegionID, O.OrderDate, O.EmployeeID
GO
-- Checking number of rows in every table and total

SELECT COUNT(*) AS CountNorth
FROM SalesInfoNorth

SELECT COUNT(*) AS CountSouth
FROM SalesInfoSouth

SELECT COUNT(*) AS CountEast
FROM SalesInfoEast

SELECT COUNT(*) AS CountWest
FROM SalesInfoWest

SELECT COUNT(*) AS CountTotal
FROM SalesInfo
GO

CountNorth
-----------
147

CountSouth
-----------
127

CountEast
-----------

417
CountWest
-----------

139
CountTotal
-----------
830
					

As you have seen in Listing 15.22, it is not necessary to insert data in the individual tables, because SQL Server detects that you are using a partitioned view and sends every row to the appropriate table (even if the table is stored in a different server).

If every individual table is stored in a different server, or instance, the view is called a distributed partitioned view. This technique provides great improvements on performance. Microsoft used this technique to execute the SQL Server 2000 performance tests sent to the Transaction Processing Council (http://www.tpc.org).

When selecting data from the partitioned view, SQL Server decides automatically which table and server must serve the request, and then divides the execution across the relevant servers .