The next few sections show you how to add, drop, and configure linked servers through system-stored procedures. sp_addlinkedserverBefore 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:
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.
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_linkedserverTo 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_dropserverYou 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:
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_serveroptionYou 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:
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]
|