Reading Data from Remote Sources in SQL Server

A second possible approach would be for the middle tier to query a view created inside SQL Server. The view should be responsible for communicating with all the different data sources, merging the results and providing a single result set to the calling application.

As shown in Figure 8-2, by moving the responsibility of merging the results to SQL Server, we can take advantage of the following facts:

• The middle tier doesn't have to be coupled to the physical implementation.

• SQL Server can provide a single result set, regardless of its physical distribution, so the middle-tier code is much easier to write and less risky in terms of security, maintenance, concurrency control, and communication.

• By processing the intermediate result sets from each of the different data sources in SQL Server, we can take advantage of the relational search engine and T-SQL constructions to make this a simpler, easier, and more maintainable solution.

Figure 8-2. Architectural model for reading data from remote sources in SQL Server.

SQL Server 2005 offers two different approaches for managing connections to remote data sources:

• Open an ad hoc connection to the external data source; or

• Manage the connection configuration information statically by setting up a linked server.

Both approaches require the remote data source to support an OLE DB data access provider. This means that you could potentially connect to another SQL Server database, the Windows File System, Microsoft Exchange Server, Windows Active Directory Service, Microsoft Excel, or any other data source with an OLE DB provider available.

Reading Data from a Remote Data Source Using Ad Hoc Queries

Ad hoc queries provide the ability to open a connection to a remote data source and query it from within T-SQL code. This one-time connection lives just throughout the duration of the operation currently being executed.

Enabling Support for Ad Hoc Queries

In SQL Server 2005, support for ad hoc queries is disabled by default as a security measure. Follow the procedure below to use the sp_configure stored procedure to configure support for ad hoc queries, or use the second procedure below to accomplish the same thing through the SQL Server Surface Area Configuration tool.

Enabling Ad Hoc Queries Using T-SQL

1.	From the Start menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Open a new query window, and enter the following code (included in the sample files as EnableAdHoc.sql in the SqlScripts folder): sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
2.	Click the Execute button.

Enable Ad Hoc Queries Using the SQL Server Surface Area Configuration Tool

1.	From the Start menu, select All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration.
2.	From the main screen, shown below, click on the Surface Area Configuration For Features link at the bottom.
3.	In the Surface Area Configuration For Features window, select the View By Instance tab on the left side of the screen
4.	Select the SQL Server instance you would like to configure and open the Database Engine tree view.
5.	Choose Ad Hoc Remote Queries from the list on the left side of the screen.
6.	Select the Enable OPENROWSET And OPENDATASOURCE Support checkbox, as shown below:

Using the OPENROWSET Function to Connect to Any Data Source

Use the OPENROWSET function to open any relational or nonrelational data source. SQL Server will always connect to the remote data source using OLE DB interfaces. The remote data source must support OLE DB, otherwise the connection will fail.

OPENROWSET allows you to specify connection configuration settings to control how SQL Server connects to the remote data source. You can also use a provider-specific query string that allows you to specify which resource you are requesting.

Connecting to a Microsoft Office Access Database by Using the OPENROWSET Function

1.	Open SQL Server Management Studio and open a New Query window. Note Northwind.mdb and Employees.xls are installed with the sample files to the following path by default: C:\Documents and Settings\User\My Documents\Microsoft Press\ Sql2005SBS_AppliedTechniques\Chapter08 If you installed your sample files to a different location, you will have to modify the code accordingly.
2.	Enter the following script in the New Query window. (Modify the file path to match the location of the Northwind.mdb file on your computer. This example is included in the sample files as OPENROWSET.sql in the SqlScripts folder.) SELECT OrderInfo.OrderID, OrderInfo.CustomerID, OrderInfo.EmployeeID FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'C:\Documents and Settings\User\My Documents\Microsoft Press \Sql2005SBS_AppliedTechniques\Chapter08\Northwind.mdb'; 'Admin';'', 'SELECT OrderID, CustomerID, EmployeeID FROM Orders') As OrderInfo
3.	Execute the script.

The OPENROWSET result set can be used anywhere in T-SQL where a table is expected.

Using SQL Server to Read Data from Multiple Data Sources

The following steps define a view with functionality similar to the ADO.NET method presented at the beginning of the chapter for connecting to multiple data sources from the middle tier. This time, however, the code will use SQL Server to read data from multiple data sources. The code for this example is included in the sample files as ReadDataFromMultiple Sources.sql in the SqlScriptExamples folder. You will need to modify the following steps to match data sources that actually exist on your network (an Access database, a SQL Server database, and an Oracle database).

1.	Open SQL Server Management Studio and connect to the SQL Server 2005 server and database where the view will be created.
2.	Type the necessary T-SQL code to declare a view: CREATE VIEW GlobalSalesData AS
3.	Open an ad hoc connection to the Microsoft Access PreSalesDB database; notice that the full path to the database file needs to be written. SELECT PreSales.CustomerID, PreSales.Date, PreSales.Amount FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'D:\PreSalesDB.MDB';'Admin';'Pass@word1', 'SELECT CustomerID, Date, Amount, Quarter FROM Opportunities ORDER BY Date DESC') As PreSales UNION Microsoft.Jet.OLEDB.4.0 is the data access provider used to access the remote data source. Replace the file path, file name, user, and password with appropriate values for your environment.
4.	Open an ad hoc connection to the Microsoft SQL Server 2005 server that hosts the SalesDB database: SELECT Orders.CustomerID, Orders.Date, Orders.Amount FROM OPENROWSET( 'SQLOLEDB', 'Server=Sales; Trusted_Connection=yes;', 'SalesDB.Sales.Orders') As Orders UNION SQLOLEDB is the data access provider used to access the remote SQL Server data source. Setting Trusted_Connection to yes means that the code authenticates the user using Windows credentials for the remote server. Replace the server and database name with appropriate values for your environment.
5.	Open an ad hoc connection to a third-party database server (in this example, an Oracle OLE DB provider is used) that hosts the PostSalesDB database: SELECT PostSales.CustomerID, PostSales.Date, PostSales.Amount FROM OPENROWSET( 'msdaora', 'Data Source=PostSalesDB;User Id=LowPrivilegeUser; Password=SomePwd;', 'SELECT CustomerID, Quarter, Date, Amount from Support') As PostSales Here, msdaora is the data access provider used to access the remote data source. Replace the data access provider, data source, user, and password with appropriate values for your environment.

The results of each query are unioned to return the complete result set.

Reading Objects with the OPENROWSET Function

The OPENROWSET function accepts two different sets of parameters to specify the connection string configurations:

• The first syntax allows you to reuse the same connection string used in any application that uses OLE DB to connect to the data source. Specify the connection string with the syntax defined by the OLE DB provider:

  OPENROWSET('provider name', 'provider specific string', 'object|query')

• In the second syntax, you specify separately the data source, user id, and password to use:

  OPENROWSET('provider name', 'datasource'; 'user_id'; 'password', 'object|query')

The last parameter (shown as object|query in the previous code examples) indicates to the remote data source what it needs to return as a response. You could specify that you want to retrieve a data object, for example a table or a view, or you could specify that you want to retrieve the result of a given query, as shown here in bold. (The code in this section is included in the sample files as OPENROWSETSyntaxExamples.sql in the SqlScriptExamples folder.)

SELECT PostSales.CustomerID, PostSales.Date, PostSales.Amount FROM OPENROWSET( 'msdaora', 'Data Source=PostSalesDB;User Id=LowPrivilegeUser; Password=SomePwd;', 'SELECT CustomerID, Quarter, Date, Amount from Support') As PostSales

You could also specify a resource to retrieve by using a three-part name, as shown below in bold, where SalesDB identifies the catalog (or database), Sales identifies the schema, and Orders identifies the table (or view) to retrieve.

SELECT Orders.CustomerID, Orders.Date, Orders.Amount FROM OPENROWSET( 'SQLOLEDB', 'Server=Sales; Trusted_Connection=yes;', 'SalesDB.Sales.Orders') As Orders WHERE Orders.Quarter = @Quarter

Important

Fully qualified names are used to correctly identify the resource or object you would like to retrieve in data sources that organize their data in catalogs, schemas, and data objects.

Fully qualified names are made of four identifiers:

<ServerName>.<CatalogName>.<SchemaName>.<ResourceName>

Using the OPENDATASOURCE Function to Connect to Any Data Source

The OPENDATASOURCE function also allows you to connect to data sources that expose data organized in catalogs, schemas, and data objects. The main difference from the OPENROWSET function is the way OPENDATASOURCE is invoked. They both return an OLE DB result set.

The OPENDATASOURCE function takes the place of the <ServerName> in a fully qualified (four-part) name to identify the object you want to retrieve from the remote data source, as shown below. Keep in mind that you must modify these examples to match your own servers and databases. The following code is included in the sample files as UseOPENDATASOURCE ToConnectToAnotherServer.sql in the SqlScriptExamples folder.

SELECT Orders.CustomerID, Orders.Date, Orders.Amount FROM OPENDATASOURCE( 'SQLOLEDB', 'Server=Sales; Trusted_Connection=yes;').SalesDB.dbo.Orders WHERE Orders.Quarter = @Quarter

In the above example, the OPENDATASOURCE function is substituted for the name of the server in the fully qualified name for the Orders table.

The following code shows how data can be extracted from an Excel file. This sample is included in the sample files as UseOPENDATASOURCEtoExtractXL.sql in the SqlScripts folder.

SELECT     Employees.FirstName,     Employees.LastName,     Employees.Title,     Employees.Country FROM OPENDATASOURCE(     'Microsoft.Jet.OLEDB.4.0',     'Excel 8.0;DATABASE=C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\EmployeeList.xls')...[Employees$] AS Employees WHERE LastName IS NOT NULL ORDER BY Employees.Country DESC

Note

Even when opening an Excel file, the four-part name syntax is used. In the SQL code above, the catalog and schema are omitted, but the dot (.) separator must still be present. That is why the query requires the "..." before the Excel page name (Employees$).

Reading Data from a Remote Data Source Using Linked Servers

Notice that every time the OPENROWSET or OPENDATASOURCE functions are used, you need to specify the connection configuration information.

In the following example, the OPENROWSET function and the OPENDATASOURCE function are both used to retrieve results from the same remote data source. This code is included in the sample files as OPENROWSETandOPENDATASOURCEUsedTogether.sql in the SqlScript Examples folder.

SELECT Orders.CustomerID, Orders.Date, Orders.Amount FROM OPENROWSET( 'SQLOLEDB', 'Server=Sales; Trusted_Connection=yes;', SalesDB.Sales.Orders) As Orders INNER JOIN OPENDATASOURCE('SQLOLEDB', 'Server=Sales; Trusted_Connection=yes;').SalesDB.Sales.OrderDetails ON Orders.OrderID = OrderDetails.OrderID WHERE Sales.Quarter = 3

Even when connecting to the same server, OPENROWSET and OPENDATASOURCE require you to specify the connection configuration settings every time.

Using Linked Servers to Connect to Any Data Source

Instead of repeating the same connection configuration settings each time the OPENROWSET or OPENDATASOURCE functions are used, SQL Server allows you to save those configuration settings and assign them an identifier, known as a linked server, so they can be reused without having to retype the connection configuration settings again. A linked server is essentially a virtual server.

Another advantage of linked servers is that they can be configured using T-SQL code or SQL Server Management Studio.

Important

Using linked servers allows you to use features not available when using OPENROWSET or OPENDATASOURCE. For example, you can:

• Manage static security

• Map login names

• Query catalog information

• Configure various connection settings, such as the default connection time-out

Configuring a Linked Server Using T-SQL

To configure a linked server using T-SQL code, you need to use the sp_addlinkedserver stored procedure. The sp_addlinkedserver stored procedure accepts several parameters to configure the remote data source.

The following code shows the complete T-SQL code necessary to add a remote SQL Server as a linked server. The code in this section is included in the sample files as LinkedServer.sql in the SqlScripts folder.

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

Important

Previous versions of SQL Server provided a stored procedure called sp_addserver used to add a reference to a remote server. This stored procedure is still available in SQL Server 2005, but only for backward compatibility. Support for this stored procedure will be removed in future versions of SQL Server. You should move forward using only sp_addlinkedserver.

The following code shows how to create a linked server to a Microsoft Excel file.

-- Linked server to an Excel file EXEC sp_addlinkedserver     @server = 'MyEmployees',     @srvproduct = 'Jet 4.0',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc = 'C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\EmployeeList.xls',     @provstr = 'Excel 8.0' GO

Here's how to create a linked server to an Access database.

-- Linked server to an Access database EXEC sp_addlinkedserver     @server = 'PreSales',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @srvproduct = 'OLEDB Provider for Jet',     @datasrc = 'C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\Northwind.mdb' GO

The code below shows how to create a linked server to a third-party database, such as an Oracle database.

-- Linked server to an Oracle database EXEC sp_addlinkedserver     @server = 'PostSales',     @srvproduct = 'Oracle',     @provider = 'msdaora',     @datasrc = 'PostSalesDB' GO

Validating Which Linked Servers Are Currently Defined

1.	Open SQL Server Management Studio and connect to a SQL Server instance.
2.	Retrieve the values from the sys.servers system view filtering by the is_linked column, as shown in this code example: SELECT * FROM sys.servers WHERE is_linked = 1
3.	Review the results returned by the sys.servers system view. It will return a row for each linked server defined on the current instance of SQL Server. The different columns indicate the current configurations for each of the linked servers. The following figure shows a partial result of this query: Important When registering linked servers, SQL Server does not check to see if the provided parameters are valid or even if the server names exist. The sp_addlinkedserver stored procedure will always succeed. In the event that you provided an incorrect parameter value, you will only find out during execution time.

Removing Configured Linked Servers

If you want to remove an already registered linked server, you can use the sp_dropserver stored procedure as shown in the following code example:

EXEC sp_dropserver 'MyEmployees' GO

The sp_dropserver stored procedure receives a single parameter, the identifier of the linked server to drop.

Configuring a Linked Server in SQL Server Management Studio

Linked servers can also be configured in SQL Server Management Studio.

1.	Open SQL Server Management Studio and connect to the server you would like to configure.
2.	Open Object Explorer by selecting Object Explorer from the View menu (as shown below) or by pressing the F8 key. Object Explorer allows you to easily find and manage all the services and components of an specific SQL Server instance.
3.	In the tree view shown, expand the specific SQL Server instance you want to configure, open the Server Objects folder, and then open the Linked Servers folder. The Linked Servers folder lists all the currently configured linked servers. The Providers folder lists all the installed OLE DB providers available to SQL Server in order for creating new linked servers.
4.	Right-click on the Linked Servers folder and select New Linked Server from the context menu to open the wizard to configure a new linked server.
5.	In the New Link Server window, shown below, configure all the parameters to access the remote data source.
6.	Click on the Security page in the Select A Page pane on the lefthand side of the window. The resulting window is shown below. If the remote data source requires authentication, you need to map the local account in the local server to local accounts in the remote server. If the authenticated login in SQL Server is a Windows account, then when calling the remote data source, SQL Server will impersonate the authenticated user. Otherwise, if the authenticated login in SQL Server is a SQL Server login, then when calling the remote data source, SQL Server will execute the remote call using the configured SQL Server service account.
7.	Click on the Server Options page in the Select A Page pane on the lefthand side of the window. The resulting window is shown below. The server options allow you to configure all the details when connecting to a remote data source. For a complete explanation of what each of these options means, see the SQL Server Books Online topic "Linked Server Properties (Server Options Page)."
8.	After configuring the linked server, press the OK button. Notice that, on the tree view in the Server Explorer panel, a new icon will be added, as shown here: When adding a new linked server, using T-SQL or SQL Server Management Studio will yield exactly the same results.

Removing a Configured Linked Server

If you want to drop an existing linked server in SQL Server Management Studio, use the following procedure.

1.	Open SQL Server Management Studio and connect to the server you would like to configure.
2.	Open Object Explorer by selecting Object Explorer from the View menu or by pressing the F8 key.
3.	In the tree view shown, expand the specific SQL Server instance you want to configure, open the Server Objects folder, and then open the Linked Servers folder.
4.	Right-click the linked server node that you would like to delete and select Delete from the context menu, or select the node and press the DELETE key on the keyboard. The Delete Object window will open. Press the OK button to delete the linked server.

Quick Comparison Between Linked Servers and Ad Hoc Queries

• Linked servers provide more granular control over connection configuration settings.

• Linked servers are managed statically, independently of the Data Manipulation Language (DML) T-SQL code that uses them.

• Linked servers can be managed by code or using SQL Server Management Studio.

• Linked servers are easier to maintain than Ad Hoc Queries. If you want an existing linked server to point to a new remote data source, you just have to change the linked server configuration. The code need not be modified.

Reading Data Using a Linked Server

There are three ways of using a linked server in T-SQL code:

• The linked server name becomes the ServerName in a fully qualified (four-part) name to identify the object you want to retrieve from the remote data source. Thus, it can be used anywhere inside T-SQL as a reference to the remote data source.

• Use the EXECUTE...AT clause to send a query to the remote data source. The EXECUTE clause is usually used for executing DDL (data definition language) statements at the remote data source or for calling remote stored procedures.

• Use the OPENQUERY function to send a query to the remote data source and retrieve a rowset. OPENQUERY can be substituted in T-SQL code any place where a table is expected.

Specifying a Linked Server in a Fully Qualified Name

The linked server identifier becomes the ServerName in the four-part reference name of a remote data source, as shown here. The code in this section is included in the sample files as LinkedServerFullyQualifiedName.sql in the SqlScripts folder.

EXEC sp_addlinkedserver @server = 'SalesServer',     @srvproduct='SQL Server' GO SELECT CustomerID, Date, Amount FROM SalesServer.SalesDB.Sales.Orders WHERE Quarter = @Quarter

The same syntax could be used to access an Excel file configured as a linked server called MyEmployees. In this example, Employee$ is the name of the Excel page being queried.

EXEC sp_addlinkedserver     @server = 'MyEmployees',     @srvproduct = 'Jet 4.0',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc = 'C:\Documents and Settings\User\My Documents\         Microsoft Press\Sql2005SBS_AppliedTechniques\         Chapter08\EmployeeList.xls',     @provstr = 'Excel 8.0' GO SELECT * FROM MyEmployees...Employees$

Specifying a Linked Server in the EXECUTE...AT Clause

The EXECUTE...AT clause has the following syntax:

EXECUTE ('query') AT LinkedServerIdentifier

Notice that any query written inside the apostrophes (') will be forwarded to the remote data source for execution. The remote data source could return an OLE DB rowset if needed.

The EXECUTE...AT construct is usually used for DDL statements, such as CREATE TABLE, CREATE PROCEDURE, or /DROP VIEW; for INSERT, UPDATE, or DELETE clauses; or to execute remote stored procedures as shown below. (This code is included in the sample files as LinkedServerExecuteAt.sql in the SqlScripts folder.)

EXEC sp_addlinkedserver @server = 'SalesServer',     @srvproduct='SQL Server' GO EXECUTE ('CalculateCommissions') AT SalesServer

In the above code, the CalculateCommissions stored procedure exists in the Sales database on a remote SQL Server. This remote SQL Server has been configured as a linked server called SalesServer.

Using OPENQUERY to Execute Pass-Through Queries

The OPENQUERY function is used in a way similar to how OPENROWSET and OPENDATASOURCE are used. It can be used anywhere in T-SQL code where a table name is expected.

Unlike OPENROWSET and OPENDATASOURCE, however, OPENQUERY takes the name of the linked server to use as an input parameter, so you don't have to specify the connection configuration settings each time you make a remote call.

The syntax for the OPENQUERY function is:

SELECT columns FROM OPENQUERY(LinkedServerIdentifier, 'query')

In the example shown below, the SalesServer linked server is a reference to the SQL Server named Sales, and it is accessed using the OPENQUERY function to retrieve all the Orders information. An inner join on the CustomerID keys is declared between the remote Orders table and the local OrderDetails table. This code is included in the sample files as OPENQUERY.sql in the SqlScriptExamples folder.

EXEC sp_addlinkedserver @server='SalesServer', @srvproduct='',     @provider='SQLNCLI', @datasrc='/books/3/165/1/html/2/SrvrName\SrvrInstance',     @catalog='Sales' GO SELECT Orders.CustomerID, OrderDetails.Date, OrderDetails.Amount FROM     OPENQUERY(SalesServer, 'SELECT * FROM ORDERS') AS Orders     INNER JOIN OrderDetails         ON Orders.CustomerID = OrderDetails.CustomerID WHERE Orders.Quarter = @Quarter