Designing a Strategy to Manage Data Across Linked Servers

Users often need to query data stored on multiple and sometimes disparate systems. You must provide a method for users to have their data available and to query the data as though stored on the system the users are initially logging into. Your solution should allow users to issue queries that reach beyond the data in their server to include the remote data they have permission to access. This solution needs to provide access to the remote server as well as the ability to address diverse data sources in a similar manner. Your solution is linked servers (see Figure 8.1).

Figure 8.1: Linked servers

Linked servers begin their processing when an application requires a result set that is at a remote server, usually in the form of a distributed query. When SQL Server 2005 recognizes that this command needs to be processed at the linked server, it forwards the request to OLE DB. OLE DB uses its provider to resolve the request at the remote server.

A linked server solution requires an OLE DB provider and an OLE DB data source. SQL Server 2005 uses the Microsoft SQL Native Client OLE DB Provider (PROGID: SQLNCLI) as the OLE DB provider. The OLE DB provider specifies the data source you query for the linked server implementation. Usually the data source for OLE DB is a database, but you can also connect to a variety of files and file formats including spreadsheets, text files, and results of full-text searches.

The OLE DB provider, a DLL, for the connecting data source must be configured and available on the server containing your SQL Server instance. Figure 8.2 depicts the linked server process and shows some of the typical sources of data.

Figure 8.2: A linked server configuration

The linked server solution most often requires a distributed query, which is a query that receives its data from more than one source. The data source for a distributed query can be on the same server or on a heterogeneous relational or nonrelational data source, but it needs to be implemented using an OLE DB provider. The secondary data source needs to expose only the data in a tabular rowset using this provider. The user accesses the secondary data source using the security context of the Microsoft Windows account of the SQL Server service they are currently logged into. Note that this impersonation is valid only for trusted or Windows logins. Impersonation is not available for SQL Server logins.

SQL Server has many options available for managing distributed queries. These options can be set either at the provider level or at the server level. When you set the options at the provider level, the linked server definitions you configure are applicable only for that OLE DB provider resource. When you set the options at the linked server level, the definitions are applicable to the specified linked server.

Next you will look at how you implement the solution as well as how permissions work using linked servers.

Implementing a Linked Server Solution

To set up linked servers, you must register the connection information as well as the data source information with your SQL Server. Once you have registered the data source, you can connect to it through a single name reference. You accomplish this process by using stored procedures. To complete the linked server connection, you must do the following:

1. Supply the name of the OLE DB provider exposing the data source. Data sources are usually databases but can include other source types such as flat files or spreadsheets. Tested data source providers for SQL Server 2005 include Microsoft SQL Native Client OLE DB Provider, Microsoft OLE DB Provider for ODBC, Microsoft OLE DB Provider for Jet, Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for Microsoft Directory Services, Microsoft OLE DB Provider for Microsoft Indexing Service, Microsoft OLE DB Provider for DB2, and Microsoft Exchange Server (Microsoft OLE DB Provider for Exchange).

2. State the necessary information the provider requires to locate the data.

3. Provide either the name of the object you want to expose as a rowset or a query to expose the desired rowset at the provider. If you are exposing an object as a rowset, it is called a remote table. If you are using a query to expose the rowset, it is termed a pass-through query.

4. You might need to also provide your SQL Server with a valid login ID to the remote data source.

You use the sp_addlinkedserver stored procedure to define an instance of SQL Server that is able to execute distributed queries. The following is the Transact-SQL syntax for the sp_addlinkedserver statement:

 sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]    [ , [ @provider= ] 'provider_name' ]    [ , [ @datasrc= ] 'data_source' ]    [ , [ @location= ] 'location' ]    [ , [ @provstr= ] 'provider_string' ]    [ , [ @catalog= ] 'catalog' ]

This is how the syntax breaks down:

• [ @server = ] 'server'   This is the name of the server you are creating the link to; 'server' is the sysname.

• [ @srvproduct = ] 'product_name'   This is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128) , with a default of NULL . If product_ name is SQL Server, you don’t need to specify provider_name , data_source , location , provider_string , and catalog .

• [ @provider = ] 'provider_name'   This is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to your data source. It must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128) , with a default of NULL . If you don’t specify provider_name , the SQL Native Client OLE DB Provider (SQLNCLI) is used.

• [ @datasrc = ] 'data_source'   This is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000) and is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

• [ @location = ] 'location'   This is the location of the database that is interpreted by the OLE DB provider. The location is nvarchar(4000) , with a default of NULL , and it is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.

• [ @provstr = ] 'provider_string'   This is the OLE DB provider–specific connection string that identifies a unique data source. The provider_string is nvarchar(4000) , with a default of NULL , and it is either passed to IDataInitialize or set as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

• [ @catalog = ] 'catalog'   This is the catalog to be used when you connect to the defined OLE DB provider. The value for catalog is sysname , with a default of NULL , and it is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider. When you are creating a linked server against an instance of SQL Server, catalog refers to the default database to which the linked server is mapped.

When using the SQL Native Client OLE DB Provider and using a named instance, you must also specify the instance for the SERVER keyword by using SERVER=servername\instancename . servername is the computer’s name on which SQL Server is running, and instancename is the name of the specific instance of SQL Server to which you are connecting.

To drop the linked server connection to the remote data source, you use the sp_dropserver stored procedure.

Warning 

If you run the sp_dropserver stored procedure on a server that has associated remote and linked server login entries, you will get an error message.

Here are a few examples using the sp_addlinkedserver stored procedure.

The first example creates a linked server named SYBEX. The linked server has a product name of SQL Server. No provider name is necessary because you are linking to the default instance of the SQL Server.

 USE master; GO EXEC sp_addlinkedserver   'SYBEX',   N'SQL Server' GO

The next example creates a linked server for the TRAINING instance of the SYBEX SQL Server. We are using SQL Native Client as the provider. Since product_name is SQL Server, you don’t need to specify provider_name, data_source, location, provider_string, and catalog; however, you need to provide the data source, which is the name of the server and the instance.

 USE master; GO EXEC sp_addlinkedserver   @server='SYBEX_TRAINING',   @srvproduct='',   @provider='SQLNCLI',   @datasrc='/books/1/342/1/html/2/SYBEX\TRAINING' GO

The next example creates a linked server for Oracle named SYBEXORA. We are using the Microsoft OLE DB provider for Oracle, MSDAORA. We have also configured the Oracle OLE DB connection using Oracle’s SQL*Net client-side components that are installed at Oracle’s network transport layer. Usually the SQL*Net connection string is mapped to an alias. In this example, the alias is 'Oraserver'.

 USE master; GO EXEC sp_addlinkedserver   @server = 'SYBEXORA',   @srvproduct = 'Oracle',   @provider = 'MSDAORA',   @datasrc = 'Oraserver' GO

The final example creates a linked server for Microsoft Access using the Microsoft OLE DB Provider for Jet. The linked server name is SYBEXAccess. In this example, we are assuming that both Microsoft Access and the Accessdb Access database are located on the local file system. We are using the Microsoft.Jet.OLEDB.4.0 provider.

 USE master; GO EXEC sp_addlinkedserver   @server = 'SYBEXAccess',   @provider = 'Microsoft.Jet.OLEDB.4.0',   @srvproduct = 'OLE DB Provider for Jet',   @datasrc = 'C:\MSOffice\Access\Accessdb.mdb' GO

You can now use SQL Server Management Studio (SSMS) to configure your options for the OLE DB provider you have linked. Here are the steps:

1. In SSMS, connect to the appropriate server instance you want to use.

2. Next, expand the Server Objects folder.

3. Expand the Linked Servers folder.

4. Expand the Providers folder. You will see a listing of all the available providers for SQL Server 2005 connectivity.

5. Right-click the provider you want to configure, and choose Properties. The Provider Options dialog box opens, and you are able to set options for your linked server connection. Note that the options are per provider. If you have multiple linked servers on one provider, they will all receive the same option assignment.

Figure 8.3 shows the Provider Options dialog box in SSMS for the Microsoft SQL Native Client OLE DB Provider.

Figure 8.3: SSMS provider options

You can specify the following options:

• Dynamic parameter   If this option is nonzero, the provider allows the ‘?’ parameter. When you set this option, you allow Microsoft SQL Server to execute parameterized queries against the provider, which might result in a performance gain for certain queries.

• Nested queries   If this option is nonzero, you indicate that the provider allows nested SELECT statements in the FROM clause. If you set this option, you allow SQL Server to delegate certain queries to the provider that require nesting SELECT statements in the FROM clause.

• Level zero only   If this option is nonzero, you can use only level 0 OLE DB interfaces against the provider.

• Allow inprocess   If this option is nonzero, you allow SQL Server to instantiate the provider as an in-process server. If you do not set this option, the default behavior of instantiating the provider outside the SQL Server process takes place. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When you instantiate the provider outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.

• Non transacted updates   If this option is nonzero, you allow SQL Server to update, even if ITransactionLocal is not available. If you enable this option, updates against the provider are not recoverable, because the provider does not support transactions.

• Index as access path   If this option is nonzero, you enable SQL Server’s ability to use indexes of the provider to fetch data. By default, indexes are used only for metadata and are never opened.

• Disallow adhoc access   If this option is set to a nonzero value, you do not allow SQL Server ad hoc access through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider. When you do not set this option, SQL Server also does not allow ad hoc access.

• Supports ‘Like’ operator   If this option is enabled, you allow SQL Server to support Transact-SQL string comparison queries using the LIKE operator.

SQL Server distributed queries have been tested with several OLE DB providers. Some tested providers that are installed with SQL Server 2005 include the following:

• SQLNCLI   Microsoft SQL Native Client OLE DB Provider for SQL Server 2005

• MSDASQL   Microsoft OLE DB Provider for ODBC

• Microsoft.Jet.OLEDB.4.0   Microsoft OLE DB Provider for Jet

• MSDAORA   Microsoft OLE DB Provider for Oracle

Other tested providers exist that have not been installed. Some of these providers include the following:

• Microsoft OLE DB Provider for Microsoft Directory Services

• Microsoft OLE DB Provider for Microsoft Indexing Service

• Microsoft OLE DB Provider for DB2

• Microsoft OLE DB Provider for Exchange

Once you have created the linked server, you can also set the linked server properties. These properties are established only on this connection. For example, if you are creating new objects at the remote server, you need to set the Rpc properties to True. Figure 8.4, which shows the Server Options page of the Linked Server properties sheet, displays the linked server properties that you can set. To get to this page, right-click your linked server name, and choose Properties.

Figure 8.4: Linked Server properties sheet, Server Options page

You can set the following options:

• Collation Compatible   This option affects distributed query execution against linked servers. If you set this option to True, SQL Server assumes that all characters in the linked server are compatible with the local server character set and sort order. SQL Server will then send comparisons on character columns to the provider. If you do not set this option, SQL Server evaluates comparisons on character columns locally.

• You should set this option if you are certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

• Data Access   This option enables and disables a linked server for distributed query access.

• Rpc   This option enables remote procedure calls (RPCs) from the given server.

• Rpc Out   This option enables RPC to the given server.

• Use Remote Collation   This option determines whether you will use the collation of a remote column or of a local server.

  If you set this option to True, the collation of remote columns is used for SQL Server data sources, and the collation specified in Collation Name is used for non–SQL Server data sources.

  If you set this option to False, distributed queries will always use the default collation of the local server, while the collation name and the collation of remote columns are ignored. False is the default.

• Collation Name   This option specifies the name of the collation used by the remote data source if Use Remote Collation is True and the data source is not a SQL Server data source. When specifying the collation name, it must be one of the SQL Server–supported collations.

  You use this option when you are accessing an OLE DB data source other than SQL Server but whose collation matches one of the SQL Server collations. The linked server must support a single collation to be used for all columns in that server.

  You should not set this option if the linked server supports multiple collations within a single data source. You should also not set this option if the linked server’s collation does not match one of the SQL Server collations.

• Connection Timeout   This option specifies the timeout value for connecting to the linked server. If the value is 0, the linked server uses the sp_configure default.

• Query Timeout   This option specifies the timeout value for queries against the linked server. If the value is 0, the linked server uses the sp_configure default.

  Note 

  If you have installed SQL Server 2005 Service Pack 2, you can test the connection ability to a linked server. In Object Explorer, right-click the linked server, and then click Test Connection.

Managing Linked Servers

A linked server solution processes your request from the remote data source using a login name and password provided by the local server. You must create a login mapping between the two linked servers using the sp_addlinkedsrvlogin system stored procedure to accomplish the remote login task. Your remote server will be linked to the local server through this login mapping. When SQL Server connects to a remotely linked server, it uses these credentials to execute stored procedures or distributed queries. The login mapping credentials are sent to the remote server at connection time.

Understanding Login Mappings

The default security for the login mapping is to use self-mapping that emulates the security credentials of the current login. When you use the sp_addlinkedserver system stored procedure to create the linked server solution, all local logins are available for self-mapping, provided the SQL Server security account is a Windows-authenticated account and security account delegation is available. Security account delegation is set in Active Directory. Figure 8.5 shows the Delegation tab in Active Directory for the service account set for trusted delegation.

Figure 8.5: Active Directory Delegation tab

Here once again are the conditions for self-mapping, the predetermined login mapping that SQL Server can automatically use to issue a query to connect to a linked server:

• The user is connected to SQL Server by using Windows authentication.

• Security account delegation is available on the client and sending server.

• The provider supports Windows authentication.

If these conditions for self-mapping are not present, you must specify a local login mapping using a Windows-authenticated login account on the local server that will map to a SQL Server– authenticated account on the remotely linked server. In this case, the remote SQL Server must use mixed authentication.

To create this login account that will be used to authenticate with the linked server if you are not using or cannot use self-mapping, you specify the needed credentials using the sp_addlinkedsrvlogin system stored procedure.

This stored procedure has the following syntax and arguments:

 sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'    [ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']    [ , [ @locallogin = ] 'locallogin' ]    [ , [ @rmtuser = ] 'rmtuser' ]    [ , [ @rmtpassword = ] 'rmtpassword' ]

Here’s how the syntax breaks down:

• @rmtsrvname =   This argument supplies the name of a linked server to which the login mapping applies.

• @useself =   The values can be TRUE, FALSE, or NULL. This argument determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8). The default is TRUE.

• Specifying TRUE indicates that logins use their own credentials to connect to rmtsrvname. In this case, the rmtuser and rmtpassword arguments will be ignored. Specifying FALSE implies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If you set rmtuser and rmtpassword to NULL, no login or password is used to connect to the linked server.

• @locallogin =   This argument specifies a login on the local server. The default value of this argument is NULL. When you use NULL, it specifies that this entry applies to all local logins that connect to rmtsrvname. If you specify an account, locallogin can be a SQL Server login or a Windows login. If you use a Windows login, it must have been granted access to SQL Server either directly or through its membership in a Windows group that is granted access.

• @rmtuser =   This argument specifies the remote login used to connect to rmtsrvname when @useself is FALSE. If the remote server is an instance of SQL Server that uses only SQL Server authentication, rmtuser is the SQL Server login. The default value of this argument is NULL.

• @rmtpassword =   This argument is the password associated with rmtuser. The default value of this argument is NULL.

After the authentication has been validated at the linked server by using the mappings that are defined by employing the sp_addlinkedsrvlogin stored procedure on the local instance of your SQL Server, it is the linked server that determines the permissions on individual objects in its (the remote) database. The local server does not determine the permissions.

Linked Server Information

The sp_helplinkedsrvlogin system stored procedure provides information about login mappings defined against a specific linked server. You can use this stored procedure to gain information regarding distributed queries and remote stored procedures.

Three system views are also available to supply linked server and linked server login information: sys.linked_logins, sys.remote_logins, and sys.servers. You can find the full explanation of the column contents of these views in SQL Server Books Online.

Distributed queries are subject to the permissions granted to the remote login by the linked server on the remote table. SQL Server does not perform any of the permission validation at compile time. Any permission violations are detected at query execution time as reported by the provider.

In Exercise 8.1 we will create a linked server on our local server for the exercises in this chapter.

Exercise 8.1: Creating a Linked Server on a Named Instance

One way to practice creating a linked server if you do not have a second SQL Server available is to create a named instance on your local server. You can find a reference for installing SQL Server 2005 in SQL Server Books Online under the topic “How To: Install SQL Server 2005.” Follow the steps, but create a named instance.

In this example, we will use NEWCATSERVER as the default installation of SQL Server 2005 and TRAINING as the named instance. You will use your server name in place of our NEWCATSERVER throughout the exercise.

Connect to the NEWCATSERVER/TRAINING instance using Object Explorer in SSMS so that it is available for the following steps.

1. At the default instance of your server, use sp_addlinkedserver to create a linked server named NEWCAT_TRAINING that connects to the named instance you created:

    --Make certain you are connected to the default instance --of your server when you execute this query. USE master; GO EXEC sp_addlinkedserver   @server='NEWCAT_TRAINING',   @srvproduct='',   @provider='SQLNCLI',   @datasrc='/books/1/342/1/html/2/Newcatserver\Training' GO

2. If you have applied Service Pack 2 to your SQL Server 2005, you can test the connection by right-clicking NEWCAT_TRAINING and choosing Test in the drop-down menu.

3. Right-click the NEWCAT_TRAINING linked server once again, and select Properties. Note the General, Security, and Server Options pages. On the Server Options page, notice that Rpc and Rpc Out are both set to False. If you are merely reading data from the remote server, this is fine. To be able to do more, such as create objects at a remote database, you will need to change both of these options to True. If you want, you can change these options to True now.

4. So that you have a database with data to connect with at NEWCAT_TRAINING, use the Copy Database Wizard to create a copy of AdventureWorks on the TRAINING named instance. You do this by right-clicking AdventureWorks and choosing Tasks Copy Database. Use AWTrain as the name of the copied database because we will be using this database in later queries.

5. So that you have a database without data to connect with at NEWCAT_TRAINING, create a new database on TRAINING. Name the new database NEWTrain.

Configuring Linked Servers for Delegation

Delegation is enabling a Windows-authenticated client that is connected to one instance of SQL Server to have its credentials forwarded to another instance of SQL Server. This instance at which the Windows user is logged into impersonates that user when communicating with another instance of SQL Server. This delegation process is required for distributed queries using self-mapping for a specific login against a specific linked server.

Here is how delegation works. The user logs into the client workstation that connects to SQL Server 2005 and wants to execute a distributed query against a database on a linked server. The user does not present any further credentials. Rather, the credentials of the user are passed through impersonation from the SQL Server the user is connecting with to the second SQL Server. This scenario of one computer connecting to another, which then connects to a third is often called a double hop. Figure 8.6 illustrates the delegation process.

Figure 8.6: In delegation, the user running a distributed query logs into a SQL Server, which impersonates that user at other connected SQL Servers

Certain requirements must be met for each of the players in the delegation scenario. The client requirements are as follows:

• The user’s Windows login must have access permissions to both SQL Server instances.

• The user’s Active Directory property, Account Is Sensitive and Cannot Be Delegated, must not be selected.

• The client computer must be using TCP/IP or named pipes network connectivity.

The first SQL Server requirements are as follows:

• The server must have an SPN registered by the domain administrator.

• The SQL Server service account must be a Windows account that is trusted for delegation in Active Directory.

• The server must be using TCP/IP or named pipes network connectivity.

• The second or linked server must be added as a linked server. An sp_addlinkedserver stored procedure like the following should be run at the first SQL Server:

   EXEC sp_addlinkedserver 'SECOND_SQLSERVER', N'SQL Server'

• The linked server logins must be configured for self-mapping. To do this, run an sp_

   addlinkedsrvlogin stored procedure like the following at the first SQL Server: EXEC sp_addlinkedsrvlogin 'SECOND_SQLSERVER','true'

The second or linked SQL Server requirements are as follows:

• If using TCP/IP network connectivity, the second server must have an SPN registered by the domain administrator.

• The second server must be using TCP/IP or named pipes network connectivity.

Using Distributed Queries

When you want to access data from multiple heterogeneous sources of data, you use distributed queries. To provide enhanced functionality for distributed queries on SQL Server 2005, distributed queries use OLE DB.

You use can use distributed queries to access data stored in multiple instances of SQL Server and data stored in a variety of heterogeneous data sources. The only stipulation for accessing data from heterogeneous sources is that it can be accessed using an OLE DB provider. Figure 8.7 illustrates the connection between a client computer, a SQL Server, and available OLE DB data sources.

Figure 8.7: Connections for OLE DB data sources

Determining the Distributed Query Method

The linked server solution provides two methods for distributed query execution: the four-part name and the OPENQUERY Transact-SQL statements.

OPENQUERY is used to execute a pass-through query on the remotely linked data source. The remote server must be an OLE DB data source. You reference OPENQUERY in the FROM clause of a query as if it were a table name. The query executes on the remote server, returning the result set to the client through the local server. Processing the query on the remote server saves on network resources.

If you need to combine data on the remote server with data on your local SQL server, you should use the four-part name. You will still be using the same concepts of the pass-through query; however, depending on the remote data type and whether you are reading or modifying data, the processing might be performed locally or remotely. If the query becomes somewhat complex using FROM and WHERE clauses, you will find SQL Server tries to do as much processing as possible on the remote server. But you should also test your execution plans or modify the query to “make it so.”

To reference your data, you need to remember that OLE DB providers use rowsets to expose tabular data. You are able to reference rowsets in Transact-SQL as if they were tables in SQL Server. Thus, you reference tables and views in external data sources using the Data Manipulation Language (DML) of Transact-SQL, which includes SELECT, INSERT, UPDATE, and DELETE statements.

Note 

Using OLE DB to access distributed queries is new to SQL Server 2005. This functionality was previously supported only with Microsoft Access.

The functionality that you can use with the remote data source is determined by the provider you are using. Since distributed queries are most often written using the four-part name method, you will write some in the next section.

Ad Hoc Distributed Query Solutions: Past and Present

At this point, you might be wondering, “Hey, what happened to OPENROWSET and OPENDATASOURCE? Why aren’t we spending time learning about those functions here?”

OPENROWSET and OPENDATASOURCE support ad hoc connections to remote data sources without implementing linked or remote servers; the link is contained within the syntax of the statement itself. When you use these functions, you enable any authenticated SQL Server 2005 login to access the provider. As an administrator, you should enable the use of ad hoc remote queries only for those providers that you determine are highly trusted and are safe to be accessed by any local login.

With the new security features built into SQL Server 2005, these functions are not enabled by default. You need to specifically enable OPENROWSET and OPENDATASOURCE support-either through the SQL Server Surface Area Configuration tool, through configuration options by using SQL Server Management Studio, or through the sp_configure system stored procedure-to use them. If you have scripts from previous versions of SQL Server that use these statements, you will need to enable them. Otherwise, Microsoft recommends that you no longer use these statements and instead use a linked or remote server solution.

Writing Distributed Queries with Four-Part Names

In Exercise 8.2, you will look at some of the types of queries that you are now able to perform using linked servers.

Exercise 8.2: Querying with Linked Servers

We assume you have completed Exercise 8.1 and have created the appropriate linked server and databases at your named instance. Recall that the linked server name is NEWCAT_TRAIN and the two databases on the TRAINING named instance that you have created are AWTrain and NEWTrain.

You will create and execute all queries from the default instance of your SQL Server.

1. Create a SELECT statement for a result set from the Vendor’s view of the AWTrain database. This query is using a four-part name:

    SELECT Name from NEWCAT_TRAINING.AWTrain.Purchasing.vVendor

2. The following shows a partial listing of the results.

   

3. The next query uses the EXECUTE statement to create a new table on the remote database NEWTrain. If you get an error on execution of this next query, you have not set Rpc and Rpc Out to True for your linked server.

    EXECUTE ( 'CREATE TABLE NEWTrain.dbo.CourseTbl (CourseID int, CourseName varchar(20)) ; ' ) AT NEWCAT_TRAINING; GO

4. Next, insert some data into the remote table:

    INSERT INTO NEWCAT_TRAINING.NEWTrain.dbo.CourseTbl (CourseID, CourseName) VALUES (1,'SQL Administration')

5. Your results should look like the following.

   

6. The next query illustrates joining local data with remote data in a query. This query references the Production.Product table in AdventureWorks and the Sales.SalesOrderDetails table in the AWTrain database on the linked server NEWCAT_TRAINING:

    --This query references the Production.Product table in --AdventureWorks and the Sales.SalesOrderDetails --table in the AWTrain database on the --linked server NEWCAT_TRAINING: SELECT p.Name, sod.SalesOrderID FROM AdventureWorks.Production.Product p INNER JOIN NEWCAT_TRAINING.AWTrain.Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID ORDER BY p.Name ;

7. Your partial output should look like this.

   

The following list states some of what you can allow and restrict in queries using linked servers:

• All queries using the standard SELECT statement with the FROM and WHERE filters are allowed.

• Queries using SELECT INTO where the table specified is at the remote server are not allowed.

• Tables with XML columns cannot be queried even if the query does not involve the XML column.

• INSERT statements are allowed against remote tables if the provider meets the OLE DB requirements for INSERT statements.

• A remote table can be updated or deleted through a cursor defined on a distributed query when the remote table is specified in the UPDATE or DELETE statement.

• READTEXT, WRITETEXT, and UPDATETEXT statements are not supported at remote tables.

• The EXECUTE statement specifying a pass-through command is supported against linked servers.

• Stored procedures are supported only using SQL Server data sources.

For a complete list, see the SQL Server Books Online topic “Guidelines for Using Distributed Queries.”