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:
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:
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
Enable Ad Hoc Queries Using the SQL Server Surface Area Configuration Tool
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
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).
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 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
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
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.
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
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
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.
Removing a Configured Linked Server
If you want to drop an existing linked server in SQL Server Management Studio, use the following procedure.
Reading Data Using a Linked Server
There are three ways of using a linked server in T-SQL code:
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