Linked Servers

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."

Linked Server Setup to Remote Datasources

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.

Figure 32.6. New Linked Server Properties specification.

Connecting to a Remote SQL Server

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' 

Establishing Linked Server Security

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:

 EXEC sp_helplinkedsrvlogin 

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!"

Querying a Linked Server

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:

 linked_server_name.catalog.schema.object_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:

 [C81124-C\DBARCH01].[Southwind].[dbo].[CustomersPlus] 

Transact-SQL with Linked Servers

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

NOTE

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 

Executing a Stored Procedure Via a Linked Server

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:

 EXECUTE  servername.dbname.owner.procedure_name  

This example executes sp_helpsrvrole , which shows a list of available fixed server roles on the 'NWServer' remote server:

 EXEC NWServer.master.dbo.sp_helpsrvrole