Collations in Distributed Queries

In Microsoft® SQL Server™ version 7.0, the local SQL Server always had only one server-wide collation (code page and sort order). All character data from remote data sources were interpreted using the local server-wide collation. However, Microsoft SQL Server 2000 supports multiple collations, which can be different for each column; each character value has an associated collation property. SQL Server 2000 interprets the collation property of character data from a remote data source and treats it accordingly.

SQL Server 2000 uses the collation of remote data for comparison and ordering operations on character data (both Unicode and non-Unicode). The collation information for remote character data is determined differently depending on whether or not the data source corresponds to a SQL Server:

• The SQL Server data OLE DB provider automatically reports the collation for each column it returns.
  
  
• For remote tables that are not in SQL Server, but for which the collation is known to be the same as one of the collations supported by SQL Server, the administrator can specify the default collation of the OLE DB data source as part of the linked server definition. SQL Server can then use the default collation as the collation for all columns returned from that linked server.

After SQL Server determines the collation of a remote character column, it follows the same rules for converting, comparing, and operating on remote table columns as it does for local columns. For more information about the rules SQL Server applies to collations and the collation names supported by SQL Server, see SQL Server Collation Fundamentals.

The linked server options defined by using sp_serveroption control if and how SQL Server uses collations from linked servers:

• UseRemoteCollation specifies whether the collation of a remote column or of a local server will be used. When TRUE, the collation of remote columns is used for SQL Server data sources, and the collation specified in CollationName is used for data sources other than SQL Server. When FALSE, distributed queries always use the default collation of the local server instance, and CollationName and the collation of remote columns are ignored.
  
  
• CollationName specifies the name of the collation used by the remote data source if UseRemoteCollation is TRUE and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server. Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations. SQL Server data sources report their column collations, and CollationName is ignored for linked servers that reference SQL Server data sources.

  Note  The only way to enable using remote collations is through the linked server options, therefore, queries constructed using ad hoc names such as OPENROWSET and OPENDATASOURCE cannot use collation information of remote character data. In addition, all linked servers in SQL Server 7.0 that are upgraded to SQL Server 2000 are set to UseRemoteCollation = False.

The following table summarizes how SQL Server determines the collation used for each column.

	Use Remote Collation = ON		Use Remote Collation = OFF
Linked server type	Collation name Not set	Collation name Set (to CollX)	Collation name Not set	Collation name Set (to CollX)
SQL Server	Remote column's actual collation	Remote column's actual collation	Default collation of local SQL Server instance	Collation of local SQL Server instance
Others	Default collation of local SQL Server instance	CollX	Default collation of local SQL Server instance	Default collation of local SQL Server instance