When you execute a distributed query, SQL Server interprets the command and then breaks it down for the destination OLE DB provider using rowset requests. A rowset is a type of database object that enables OLE DB data providers to support data with a tabular format. As their name implies, rowset objects represent a set of rows and columns of data. After creating the rowset objects, the OLE DB provider calls the data source, opens the necessary files, and returns the requested information as rowsets. SQL Server then formats the rowsets as result sets and adds any applicable output parameters.

Note

With SQL-92, user connections must have the ANSI_NULLS and ANSI_WARNINGS options before they can execute distributed queries. Be sure to configure these options, if necessary. For more information, see the section titled "Configuring User and Remote Connections" in Chapter 6.

You can create simple distributed queries quickly by making your own rowsets. To do this, you use the Openrowset function. When you use this function, you do not need to use linked servers, and you can use the Openrowset function in place of a table in a query if you pass parameters that identify the OLE DB data source and provider.

You use the Openrowset function in the same way that you use virtual tables; simply replace the virtual table reference with an Openrowset reference. Example 11-1 shows the syntax and usage of Openrowset.

SELECT selection FROM OPENROWSET(rowset_options) AS table_alias

( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password   
   | 'provider_string' }                                        
      , { [ catalog. ] [ schema. ] object                      
   | 'query' }                                                 
   | BULK 'data_file' ,                                       
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]    
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }                
} )                                                             
                                                               
<bulk_options> ::=                                             
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }]      
   [ , ERRORFILE = 'file_name' ]                                
   [ , FIRSTROW = first_row ]                                   
   [ , LASTROW = last_row ]                                     
   [ , MAXERRORS = maximum_errors ]                             
   [ , ROWS_PER_BATCH = rows_per_batch ]                        
                                                    
Usage

GO                                                             
SELECT a.*                                                     
FROM OPENROWSET('SQLOLEDB','Pluto';'netUser';'totem12',        
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')  
AS a                                                           
GO
SELECT o.*                                                   
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:                 
northwind.mdb';'Admin';", 'Orders')                            
AS o

The BULK rowset provider is similar to the BULK INSERT statement. The data_file parameter is used to specify the data file from which data will be copied into the target table. A format file is required to define the column types in the result set, except when you use SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB. SINGLE_BLOB returns the contents of the data file as a single-row, single-column rowset of type varbinary (max). SINGLE_CLOB reads the data file as ASCII text and returns the contents of the data file as a single-row, single-column rowset of type varchar (max). SINGLE_NCLOB reads the data file as Unicode text and returns the contents as a single-row, single-column rowset of type nvarchar (max). Both SINGLE_CLOB and SINGLE_NCLOB use the collation of the current database.

When the OPENROWSET BULK option is used with an INSERT statement, you can use standard table hints, such as TABLOCK, as well as the special BULK INSERT table hints: IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. When you use the BULK rowset provider with OPENROWSET, you must either specify column aliases in the FROM clause or specify column names in the format file. The syntax for the SELECT statement with the table alias then becomes:

SELECT selection FROM OPENROWSET(BULK rowset_options) AS 
table_alias[(column1_alias, column2_alias,...)]

Distributed transactions are transactions that use distributed queries or remote procedure calls (RPCs). As you might expect, distributed transactions are more involved than distributed queries, primarily because you need a mechanism that ensures that transactions are committed uniformly or rolled back on all the linked servers. For example, if you start a transaction that updates databases on three different server instances, you want to make certain that the transaction is committed when it has completed successfully or that the transaction is rolled back if an error occurs. In this way, you ensure the integrity of the databases involved in the distributed transaction.

On SQL Server, three components are required for distributed transactions to be handled properly:

Note

Applications other than SQL Server can use the Distributed Transaction Coordinator. If you try to analyze Distributed Transaction Coordinator performance, you should note which applications besides SQL Server are using Distributed Transaction Coordinator.

Each server instance involved in a distributed transaction is known as a resource manager. Resource managers coordinate transactions through a transaction manager, such as the Microsoft Distributed Transaction Coordinator. You can use other transaction managers if they support the X/Open XA specification for distributed transaction processing.

You handle distributed transactions in much the same manner as local transactions. Applications start distributed transactions in several ways:

At the end of the transaction, the application requests that the transaction be either committed or rolled back. To ensure that the transaction is handled properly on all servers, even if problems occur during the transaction, the transaction manager uses a commit process with two phases:

SQL Server applications manage distributed transactions either through Transact-SQL or through the SQL Server database application programming interface (API). SQL Server itself supports distributed transactions using the ITransactionLocal (local transaction) and ITransactionJoin (distributed transactions) OLE DB interfaces as well as the rowset objects discussed previously. If an OLE DB provider does not support ITransactionJoin, then only read-only procedures are allowed for that provider. Similarly, the types of queries you can execute on a linked server depend on the OLE DB provider you are using.

With distributed queries and transactions, you can use most data manipulation language (DML) commands, such as SELECT, INSERT, UPDATE, and DELETE. You cannot, however, use data definition language (DDL) commands, such as CREATE, DROP, or ALTER. If you need to use DDL commands on linked servers, you may want to create stored procedures and then execute these stored procedures remotely, as necessary.

You can use the EXECUTE statement to execute commands and stored procedures on a linked server. Example 11-2 shows the syntax for using EXECUTE in this way.

EXEC [UTE] ( { @string_variable | [ N ] 'command_string' } [ + ...n ] 
    [ {, { value | @variable [ OUTPUT ] } } [...n] ] )  
    [ AS { LOGIN | USER } = ' name ' ]  
    [ AT linked_server_name ] [;]

EXEC ( 'SELECT * FROM william.sales') AT ORADBSVR8;

The Distributed Transaction Coordinator service must run on each server that handles distributed transactions, and you usually will want the service to start automatically when the system starts. This ensures that the distributed transactions are executed as expected. By using SQL Server Configuration Manager, you can control the Distributed Transaction Coordinator service just as you do other SQL Server-related services. For details, see the section titled "Configuring SQL Server Services" in Chapter 3.

You can view the Distributed Transaction Coordinator service in SQL Server Management Studio by completing the following steps: