Registering nicknames for Microsoft SQL Server tables and views

For each Microsoft SQL Server remote server definition that you register, you must register a nickname for each table or view that you want to access. Use these nicknames, instead of the names of the data source objects, when you query the Microsoft SQL Server remote servers.

Before you begin

To ensure that the federated database has current and complete statistics, execute the Microsoft SQL Server sp_createstats stored procedure and the Microsoft SQL Server CREATE STATISTICS command from the Microsoft SQL Server database before creating the nickname.

The sp_createstats stored procedure gathers statistics on all of the default columns in a table in an Microsoft SQL Server data source, but does not gather statistics for columns that appear first within an index. To ensure that the federated database has complete statistics on the Microsoft SQL Server table, you also must use the Microsoft SQL Server CREATE STATISTICS command to gather statistics for each column that appears first in an index.

When you use the CREATE STATISTICS command from the Microsoft SQL Server database, you must give the statistic the same name for the column on which the statistics are being collected. By giving the statistic the same name as the column, you ensure that when you register the nickname with the CREATE NICKNAME statement, the federated database reads the statistics collected by the Microsoft SQL Server CREATE STATISTICS command.

Procedure

Issue the CREATE NICKNAME statement from the command line.

For example:

CREATE NICKNAME nickname 

FOR server_definition_name."remote_schema"."remote.table";

When you create the nickname, the federated server queries the data source catalog using the nickname. This query tests the connection to the data source table, view, or synonym. If the connection does not work, you receive an error message.

What to do next

Repeat this step for each Microsoft SQL Server table or view that you want to create a nickname for.