As mentioned before, you must first create a linked server before you can work with data from a remote SQL Server or another OLE DB datasource, such as Excel, Access, or Oracle. A linked server consists of an OLE DB or other datasource that is registered on the local SQL Server. After this is set up, Transact-SQL statements can be sent directly to a remote datasource via this linked server reference as if it were a normal relational table on SQL Server. This will be needed for both distributed queries and distributed transactions. For an extensive description of linked servers, see Chapter 19, "Managing Linked and Remote Servers."
To enable you to execute Transact-SQL statements on a remote SQL Server or OLE DB datasource, you must create a link to the server or datasource. You can do so using either Enterprise Manager or the sp_addlinkedserver system-stored procedure:
sp_addlinkedserver [@server =] 'server' [, [@srvproduct =] 'product_name'][, [@provider =] 'provider_name'] [, [@datasrc =] 'data_source'] [, [@location =] 'location'] [, [@provstr =] 'provider_string'] [, [@catalog =] 'catalog']
This defines a remote datasource as a linked server (like an Excel spreadsheet) and includes the OLE DB provider.
As you can see in the following code, not all of these parameters are required. You simply provide the ones needed for the particular type of OLE DB provider. Some parameters require less information than others to establish connectivity to that remote datasource. In the Customer Orders report example (Figure 32.5), you will need to set up a linked server called ExcelSW with sp_addlinkedserver as follows:
/* Set up of an Excel linked server */ EXEC sp_addlinkedserver 'ExcelSW', /* linked server name you want to use*/ 'Jet Excel', /* product name ? can be anything */ 'Microsoft.Jet.OLEDB.4.0', /* OLE provider name */ 'd:\SWCustomers.xls', /* datasource name */ NULL, /* location not needed in this case */ 'Excel 8.0', /* Provider string if needed */ NULL /* catalog name if needed */ go
To see the linked servers that have been defined on this SQL Server, simply use sp_linkedservers.
EXEC sp_linkedservers Go
The sp_linkedservers execution provides the list of all linked servers on this SQL Server:
SRV_Name SRV_Provider SRV_Product SRV_Datasource etc.. C81124-C\DBARCH01 SQLOLEDB SQL Server C81124-C\DBARCH01 ExcelSW Microsoft.Jet.OLEDB.4.0 Jet Excel d:\SWCustomers.xls Excel 8.0 repl_distributor SQLOLEDB SQL Server C81124-C\DBARCH01
As you can see, the list now contains the linked server that was just created (ExcelSW). However, you won't be able to use this linked server yet. A linked server login will have to be created for SQL Server to actually get to the datasource. You have essentially established the path to the datasource with sp_addlinkedserver.
To do this from Enterprise Manager, it is a simple one-step process from the linked server tree node. Navigate to the Security node and then right-click the Linked Servers Node (or choose the New Linked Server option from the Action menu pull-down list). Just provide the same information as is required for the type of OLE DB provider you want to access. As you can see from Figure 32.6, the General properties tab allows easy entry of a new linked server and a datasource file.
You do not need to specify the provider_name, data_source, location, provider_string, or catalog name when you are connecting to a SQL Server 6.5 (or higher) remote datasource. It is a simple process of providing the server name and the product name. As an example, you will create a linked server for another SQL Server named 'NWServer'.
sp_addlinkedserver 'NWServer', 'SQL Server'
When a remote/distributed query is executed, the local SQL Server logs into the remote SQL Server or datasource on behalf of the user. Therefore, it might be necessary to establish security between the local and remote datasources. However, if the user's login ID and password exist on both the local and remote SQL servers, the local SQL Server can use the account information of the user to log into the remote SQL Server.
To establish login IDs and passwords between local and remote SQL Servers (or datasources), use the sp_addlinkedsrvlogin system-stored procedure.
Keep in mind that sp_addlinkedsrvlogin does not create user accounts. It merely maps a login account created on the local server to an account created on the remote server. In some cases, it utilizes a default system login placeholder (like with the Admin account with Excel spreadsheets). Again, for more detailed information on linked servers, refer to Chapter 19.
The syntax for the sp_addlinkedsrvlogin system-stored procedure is as follows:
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' [ , [ @useself = ] 'useself' ] [ , [ @locallogin = ] 'locallogin' ] [ , [ @rmtuser = ] 'rmtuser' ] [ , [ @rmtpassword = ] 'rmtpassword' ]
In the next example, you will set up the mapping required to use a SQL Server login ("sa" in this case) to access a linked server that is an Excel spreadsheet. Again, you will be setting up the linked server login for the ExcelSW linked server.
EXEC sp_addlinkedsrvlogin 'ExcelSW', /* remote/linked server name */ 'false', /* don't use user's own info */ 'sa', /* use already created login id */ 'Admin', /* maps to login id (for Excel) */ NULL /* no password, in this example */
Now the linked server reference is registered to SQL Server and the login/access has been mapped to enable it to be used.
You can also use a system-stored procedure to quickly find out what linked server logins have been defined. Simply execute the procedure without parameters to see all that have been defined on this local SQL Server:
The list of linked server logins are easily viewed as follows:
Linked Server Local Login Is Self Mapping Remote Login C81124-C\DBARCH01 NULL 1 NULL ExcelNW NULL 1 NULL ExcelNW sa 0 Admin ExcelSW NULL 1 NULL ExcelSW sa 0 Admin NWServer NULL 1 NULL repl_distributor NULL 0 distributor_admin
If you haven't yet added the login for the linked server that is a valid login at the referenced server, you will probably get the following error message on your first query attempt:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
SQL Server simply has nothing to map to on the linked/remote server side. You must resolve this by adding a valid user ID with the appropriate permissions for the linked server to use when attempting data access.
Keep in mind that when you complete the linking process, SQL Server really keeps these data resources linked in many ways. Most importantly, it keeps the schema definition linked. In other words, if the schema of a remote table on a linked server changes, any server that has links to it also knows the change. Even when the linked server's schema comes from something such as Excel, if you change the Excel spreadsheet in any way, that change will be automatically reflected back at the local SQL Server. This is extremely significant from a metadata (schema's) integrity point of view. This is what is meant by "completely linked!"
When you write a distributed query (or a distributed transaction for that matter), you must use a four-part name to refer to the linked objects. The linked server is said to conform to the IDBSchemaRowset interface. (It allows for the schema information to be retrieved from the remote server.) If the linked server doesn't conform to this interface, you will have to do all access using pass-through queries in the OPENDATASOURCE or OPENROWSET function. However, in most cases, you will be using this four-part name:
The name can be broken down as follows:
|linked_server_name||The unique network-wide name of the linked server [Servername\instancename|Servername]|
|catalog||The catalog or database in the OLE DB that contains the object|
|schema||The schema or object owner|
|object_name||The name of the table or data object|
For example, if you want to use the CustomersPlus table that is owned by the database owner (dbo) in the Southwind database on the C81124-C\DBARCH01 linked SQL Server, use the following four-part name to reference the CustomersPlus table:
You can use the following Transact-SQL statements with linked servers:
SELECT statement with a WHERE clause or a JOIN clause
INSERT, UPDATE, and DELETE statements
Restrictions for use of Insert, Update, and Delete exist with certain OLE DB providers. Some OLE DB providers only allow reads, such as with flat files.
You cannot use the following:
CREATE, ALTER, or DROP statements
An ORDER BY clause in a SELECT statement if a large object column from a linked table is in the select list of the SELECT statement
READTEXT, WRITETEXT, and UPDATETEXT statements
Whenever possible, SQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB datasource. SQL Server does not default to scanning the base table into SQL Server and performing the relational operations itself. SQL Server will actually query the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, will push as much as possible to the OLE DB provider.
To execute the original Customer Orders report-distributed query, you can execute the following SQL statement. As you will recall, the statement will need to join the Excel spreadsheet file (SWCustomers.xls) to the SQL Server table named CustomersPlus, resulting in this week's Customer Orders report for the sales director. The linked server and linked server login needed to fulfill this access have been set up. The following query generates the required report:
USE Southwind Go SELECT a.CustomerID, a.CompanyName, b.ContactName, b.Address, b.City, b.Region, b.PostalCode, b.Country, b.Phone, b.Fax, a.YTDBusiness FROM CustomersPlus AS a INNER JOIN [ExcelSW]...[SWCustomers$] AS b ON a.CustomerID = b.CustomerID go
The Customer Orders report now can generate a valid distributed resultset as follows:
CustomerID CompanyName ContactName Address City Region . . . YTDBusiness BLAUS Blauer See Delikatessen Vilay Sithongkang 4394 Water Bridge Concord CA 3239.8000 CHOPS Chop-suey Chinese Martin Sommer 6 of One Half a St. Berkeley CA 12886.3000 PICCO Piccolo und mehr Adam Greifer 121 All the way up Ct. Los Angeles CA 26259.9500
An alternative method of executing a distributed SQL Query is to use the OPENQUERY syntax. Following is an example of a remote/distributed query against the ExcelSW linked server:
SELECT CustomerID, Address FROM OPENQUERY([ExcelSW], 'SELECT CustomerID, Address FROM [SWCustomers$]') Go
It is possible to execute a stored procedure via a linked server. The server hosting the client connection will accept the client's request and send it to the linked server. The EXECUTE statement must contain the name of the linked server as part of its syntax:
This example executes sp_helpsrvrole, which shows a list of available fixed server roles on the 'NWServer' remote server: