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');