Adding a Linked Server

There are two ways to add a linked server. You can create one by using SQL Server Enterprise Manager interface or by issuing SQL commands. If you use SQL Server Enterprise Manager, you can configure a wider set of options. For more information about adding and using linked servers, see Configuring Linked Servers and Establishing Security for Linked Servers.

Enterprise Manager

Adding a Linked Analysis Server Using Transact-SQL

Use the system stored procedure sp_addlinkedserver to add a linked Analysis server using SQL commands.

Syntax

Linking to Microsoft® SQL Server™ 2000 Analysis Services requires more specific syntax than that provided in the SQL Server documentation. Note the required program ID MSOLAP.

sp_addlinkedserver @server = 'server', @srvproduct = 'product_name',
    @provider = 'MSOLAP', @datasrc = 'data_source',
    @catalog = 'catalog'

Arguments

@server = 'server'

This is the name of the Analysis server as it is referenced in SQL Server. This name is used to identify the linked server in pass-through queries.

@srvproduct = 'product_name'

The product name of the OLE DB data source to be added as a linked server. Leave the product_name value NULL.

@provider = 'MSOLAP'

The progID of the OLE DB Provider for Analysis Services.

@datasrc = 'data_source'

The network name of the computer hosting Analysis Services.

@catalog = 'catalog'

The name of the Analysis Services database that contains cubes to be queried.

Example

Use the following example to create a linked server reference using SQL Server:

/* Remove any previous references to the linked server */
EXEC sp_dropserver 'LINKED_OLAP'

EXEC sp_addlinkedserver
     @server='LINKED_OLAP',   /* local SQL name given to the 
                                 linked server */
     @srvproduct='',          /* not used */
     @provider='MSOLAP',      /* OLE DB provider */
     @datasrc='OLAPSRV',      /* analysis server name (machine name) */
     @catalog='foodmart'      /* default catalog/database */

/* Two additional procedures obtain information about the
   tables and columns available in the cube. It is not
   necessary to use them to complete the link.*/

/* This provides schema rowset information about
   the dimensions available from the linked server */
EXEC sp_tables_ex
   @table_server='LINKED_OLAP'

/* This provides schema rowset information about the 
   measures and levels of the dimensions 
   available from the linked server */
EXEC sp_columns_ex 
   @table_server='LINKED_OLAP',
   @table_name='Sales'