CREATE SERVER statement - Examples for the Microsoft SQL Server wrapper

Use the CREATE SERVER statement to register server definitions for the Microsoft SQL Server wrapper. This topic provides a complete example with the required parameters, and an example with additional server options.

The following example shows you how to register a server definition for a Microsoft SQL Server wrapper by issuing the CREATE SERVER statement: 
CREATE SERVER sqlserver TYPE MSSQLSERVER VERSION 2000 WRAPPER wrapper_name 
    OPTIONS (NODE 'sqlnode', DBNAME 'africa');
sqlserver
A name that you assign to the Microsoft SQL Server remote server. Duplicate server definition names are not allowed.
TYPE MSSQLSERVER
Specifies the type of data source to which you are configuring access. For the Microsoft SQL Server wrapper, the server type must be MSSQLSERVER.
VERSION 2000
The version of Microsoft SQL Server database server that you want to access.
WRAPPER wrapper_name
The wrapper name that you specified in the CREATE WRAPPER statement.
NODE 'sqlnode'
The name of the node where the Microsoft SQL Server remote server resides. On federated servers that run Windows, the System DSN name for the Microsoft SQL Server remote server that you are accessing. On federated servers that run UNIX, the node that is defined in the .odbc.ini file.

This value is case sensitive.

Although the name of the node is specified as an option in the CREATE SERVER statement, it is required for Microsoft SQL Server data sources.

DBNAME 'africa'
The name of the Microsoft SQL Server database that you want to access. This value is case sensitive.

Although the name of the database is specified as an option in the CREATE SERVER statement, it is required for Microsoft SQL Server data sources.

Server options

When you create a server definition, you can specify additional server options in the CREATE SERVER statement. The server options can be general server options and Microsoft SQL Server-specific server options.

The COLLATING_SEQUENCE server option specifies whether the data source uses the same collating sequence as the federated server or a different collating sequence. On a Microsoft SQL Server database server that is running Windows NT or Windows 2000, the default collating sequence is case insensitive (for example, 'STEWART' and 'StewART' are considered equal). To guarantee correct results from the federated server, set the COLLATING_SEQUENCE server option to 'I'. This setting indicates that the Microsoft SQL Server data source is case insensitive.

The federated server does not push down queries if the results that are returned from the data sources will be different from the results that are returned when processing the query at the federated server. When you set the COLLATING_SEQUENCE server option to 'I', the federated server does not push down queries that contain string data or expressions and that also contain any of the following clauses, predicates, or functions:
  • GROUP BY clauses
  • DISTINCT clauses
  • Basic predicates, such as equal to (=)
  • Aggregate functions, such as MIN or MAX
The following example shows how to specify the COLLATING_SEQUENCE server option: 
CREATE SERVER sqlserver TYPE MSSQLSERVER VERSION 2000 WRAPPER mssqlodbc3 
       OPTIONS (NODE 'sqlnode', DBNAME 'africa', COLLATING_SEQUENCE 'I');