Adding, Dropping, and Configuring Linked Servers


The next few sections show you how to add, drop, and configure linked servers through system-stored procedures.

sp_addlinkedserver

Before you can access an external datasource through SQL Server, it must be registered inside the database as a linked server. You use the sp_addlinkedserver stored procedure for this purpose. Only users with the sysadmin or setupadmin fixed-server roles can run this procedure.

SQL Server 2000 ships with a number of OLE DB providers, including providers for Oracle databases, DB2 databases, Access databases, other SQL Server 6.5/7.0 databases, as well as databases that can be reached through ODBC and JDBC. SQL Server also comes with OLE DB providers for Microsoft Excel spreadsheets and Indexing Service.

Some of the arguments for sp_addlinkedserver are needed only for certain OLE DB providers. Because of the number of different options and settings available, refer to the documentation for the OLE DB provider to determine which arguments must be provided and the appropriate strings:

 sp_addlinkedserver [@server =] '  server  '    [, [@srvproduct =] '  product_name  '][, [@provider =] '  provider_name  ']   [, [@datasrc =] '  data_source  '] [, [@location =] '  location  ']   [, [@provstr =] '  provider_string  '] [, [@catalog  =  ] '  catalog  '] 

The following list describes each element of the syntax:

Server The name of the linked server that will be added.
product_name The product name of the OLE DB provider. If this argument is set to 'SQL Server' , then only the @server argument is required. For all other OLE DB providers delivered with SQL Server, you can ignore this parameter.
provider_name The unique programmatic identifier (PROGID). This value must match the PROGID in the Registry for the particular OLE DB provider. The following list shows the OLE DB providers delivered with SQL Server and the corresponding values for this argument:
  SQL Server SQLOLEDB
  Access/Jet/Excel Microsoft.Jet.OLEDB.4.0 Spreadsheets
  ODBC MSDASQL
  DB2 DB2OLEDB
  Oracle MSDAORA
  File System MSIDXS (Indexing Service)
data_source A datasource that points to the particular version of the OLE DB source. For example, for setting up an Access linked server, this argument holds the path to the file. For setting up a SQL Server linked server, this argument holds the machine name of the linked SQL Server. The following list shows the OLE DB providers delivered with SQL Server and the corresponding values for this argument:
  SQL Server Network name of SQL Server
  Access/Jet/Excel Full pathname to the file Spreadsheets
  ODBC System DSN or ODBC connection string
  Oracle SQL*Net alias
  File System(Indexing Service) Indexing Service catalog name
Location The location string possibly used by the OLE DB provider.
provider_string The connection string possibly used by the OLE DB provider.
Catalog The catalog string possibly used by the OLE DB provider.

Figure 19.4 depicts the overall technical architecture of what is being enabled via linked servers and providers.

Figure 19.4. Linked Servers provider architecture.

graphics/19fig04.gif

This allows datasources to be accessed from within SQL Server 2000 with the highest degree of efficiency possible.

The following example adds an Oracle linked server called 'ORACLE_DATABASE' that will connect to the database specified by the SQL*Net string 'my_sqlnet_connect_string' :

 EXEC sp_addlinkedserver @server='ORACLE_DATABASE',  @srvproduct='Oracle', @provider='MSDAORA', @datasrc='my_sqlnet_connect_string' 

The next example adds an Access linked server called 'ACCESS_DATABASE' that will connect to the database 'Foodmart.mdb' stored in the C:\temp directory:

 EXEC sp_addlinkedserver @server='ACCESS_DATABASE',  @srvproduct='Access', @provider='Microsoft.Jet.OLEDB.4.0', @datasrc='C:\temp\Foodmart.mdb' 

This example adds a SQL Server linked server that resides on the 'SQL_SERVER_DB' machine:

 EXEC sp_addlinkedserver @server='SQL_SERVER_DB',  @srvproduct='SQL Server' 

This example adds an Excel 8.0 spreadsheet as a linked server:

 /* Set up of an Excel linked server */  EXEC sp_addlinkedserver         'ExcelSW',    /* linked server name you want to use*/         'Jet Excel',   /* product name  can be anything */         'Microsoft.Jet.OLEDB.4.0', /* OLE provider name */         'd:\SWCustomers.xls',  /* datasource name */         NULL,  /* location not needed in this case */         'Excel 8.0',  /* Provider string if needed */         NULL    /* catalog name if needed */ go 

This example adds an ODBC datasource as a linked server called 'ODBC_with_DATA_SOURCE' . The ODBC connection string must be registered on the local server to use this linked server:

 EXEC sp_addlinkedserver @server='ODBC_with_DATA_SOURCE',  @srvproduct='ODBC', @provider='MSDASQL', @datasrc='My_ODBC_connection_string' 

This example adds an ODBC datasource as a linked server called 'ODBC_with_PROVIDER_STRING' . Unlike the previous example, an ODBC datasource does not need to exist. The information normally stored as an ODBC datasource is stored in the provstr argument:

 EXEC sp_addlinkedserver @server='ODBC_with_PROVIDER_STRING',   @srvproduct='ODBC', @provider='MSDASQL',  @provstr='DRIVER={SQL Server}; SERVER=MyServer; UID=sa;PWD=;' 

sp_linkedserver

To see the linked servers that have been defined on this SQL Server, simply use sp_linkedservers .

 EXEC sp_linkedservers  Go 

The sp_linkedservers execution provides the list of all linked servers on this SQL Server:

 SRV_Name          SRV_Provider            SRV_Product SRV_Datasource     ...  ----------------- ----------------------- ----------- ------------------ ... C81124-C\DBARCH01 SQLOLEDB                SQL Server  C81124-C\DBARCH01  ... ExcelSW           Microsoft.Jet.OLEDB.4.0 Jet Excel   d:\SWCustomers.xls ... repl_distributor  SQLOLEDB                SQL Server  C81124-C\DBARCH01  ... 

As you can see, the list now contains the linked server that was just created (ExcelSW). However, you won't be able to use this linked server yet. A linked server login will have to be created for SQL Server to actually get to the datasource. You have essentially established the path to the datasource with sp_addlinkedserver .

sp_dropserver

You can unregister linked servers using sp_dropserver . Only members of the sysadmin and setupadmin fixed-server roles can execute this stored procedure:

 sp_dropserver [@server =] '  server  ' [, [@droplogins =] {'droplogins'  NULL}] 

The following list describes each element of the syntax:

server The linked server that will be unregistered.
droplogins Specifies that the logins associated with the server should be dropped. If this argument is not specified, then the server will only be dropped if logins do not exist for this linked server.

The following example unregisters an Oracle, Access, and SQL Server database:

 EXEC sp_dropserver @server='ORACLE_DATABASE', @droplogins='droplogins'  EXEC sp_dropserver @server='ACCESS_DATABASE' EXEC sp_dropserver @server='SQL_SERVER_DB',@droplogins='droplogins' 

sp_serveroption

You can configure linked servers with sp_serveroption . Only users with the sysadmin or setupadmin fixed-server roles can run this procedure:

 sp_serveroption [[@server =] '  server  '] [,[@optname =] '  option_name  ']  [,[@optvalue =] '  option_value  '] 

The following list describes each element of the syntax:

server The linked server that will be affected by this option.
option_name The name of the option to be configured. The valid option names follow:
  'collation compatible' If the optvalue is set to TRUE , SQL Server assumes the linked server has the same character set and collation sequence. Only set this option to true if you are sure the character sets and collation are identical.
  'connect timeout' The length of time, in seconds, to wait before timing out connection attempt to linked server. If , uses sp_configure default value.
  'data access' If the optvalue is set to TRUE , distributed queries will be allowed if the OLE DB provider supports it. If the optvalue is set to FALSE , distributed queries will be disabled on this linked server.
  'dist' If the optvalue is set to TRUE , this specifies that the linked server is a distributor (used for replication).
  'dpub' If the optvalue is set to TRUE , this specifies that the linked server is a remote publisher to this distributor (used for replication).
  'lazy schema If the optvalue is set to TRUE , skips check validation' the schema of remote tables at the beginning of the query.
  'pub' If the optvalue is set to TRUE , this specifies that the linked server is a publisher (used for replication).
  'query timeout' Length of time, in seconds, to wait before timing out queries against linked server. If , uses sp_configure default value.
  'sub' If the optvalue is set to TRUE , this specifies that the linked server is a subscriber (used for replication).
  'rpc' If the optvalue is set to TRUE , this allows RPCs from the linked server.
  'rpc out' If the optvalue is set to TRUE , this allows RPCs to the linked server.
  'system' For internal use only.
  'use remote collation' If the optvalue is set to TRUE , will use the collation of remote columns for SQL Server datasources or the specified collation name for non-SQL Server sources. If FALSE , will use the local server default collation.
  'collation name' If use remote collation is set to TRUE and the linked server is not a SQL Server, specifies the name of the collation to be used on the linked server. Use this option when the OLE DB datasource has a collation that matches one of the SQL Server collations.
option_value The value of this option. Valid values are TRUE (or ON ) and FALSE (or OFF ), a nonnegative integer for the connect timeout and query timeout options, or a collation name for the collation name option.

This example disables distributed queries to the ORACLE_DATABASE linked server:

 EXEC sp_serveroption @server='ORACLE_DATABASE',  @optname='data access', @optvalue='FALSE' 

This example enables RPCs to the SQL_SERVER_DB linked server:

 EXEC sp_serveroption @server='SQL_SERVER_DB',  @optname='rpc out', @optvalue='TRUE' 

To set the query timeout to 60 seconds for the SQL Server datasource, execute the following command:

 EXEC sp_serveroption  'SQL_SERVER_DB ', 'query timeout', 60 

To display the options currently enabled for a linked server, use sp_helpserver, use :

[View full width]
 
[View full width]
EXEC sp_helpserver @server='SQL_SERVER_DB' GO name network_name status id collation_name connect_timeout query_timeout -------------- -------------- --------------------------------------------- ---- graphics/ccc.gif -------------- --------------- ------------- SQL_SERVER_DB SQL_SERVER_DB rpc,rpc out,data access,use remote collation 1 NULL graphics/ccc.gif 0 60


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net