exec sp_addlinkedserver @server = ‘Chron1′ ,
@srvproduct = ‘Sybase’,
@provider = ‘MSDASQL’,
@datasrc = ‘Chron1′
sp_addlinkedserver [ @server= ] ‘server‘
[ , [ @srvproduct= ] ‘product_name‘ ]
[ , [ @provider= ] ‘provider_name‘ ]
[ , [ @datasrc= ] ‘data_source‘ ]
[ , [ @location= ] ‘location‘ ]
[ , [ @provstr= ] ‘provider_string‘ ]
[ , [ @catalog= ] ‘catalog‘ ]
The following table shows the ways that a linked server can be set up for data sources accessible through OLE DB. A linked server can be set up using more than one way for a given data source; there may be more than one row for a data source type. This table also shows thesp_addlinkedserver parameter values to be used for setting up the linked server.
| Remote OLE DB data source | OLE DB provider | product_name | provider_name | data_source | location | provider_string | catalog |
|---|---|---|---|---|---|---|---|
| SQL Server | Microsoft OLE DB Provider for SQL Server | SQL Server (1) (default) |
- | - | - | - | - |
| SQL Server | Microsoft OLE DB Provider for SQL Server | SQL Server | SQLOLEDB | Network name of SQL Server (for default instance) | - | - | Database name (optional) |
| SQL Server | Microsoft OLE DB Provider for SQL Server | - | SQLOLEDB | Servername\instancename (for specific instance) | - | - | Database name (optional) |
| Oracle | Microsoft OLE DB Provider for Oracle | Any (2) | MSDAORA | SQL*Net alias for Oracle database | - | - | - |
| Access/Jet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path name of Jet database file | - | - | - |
| ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | System DSN of ODBC data source | - | - | - |
| ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | - | - | ODBC connection string | - |
| File system | Microsoft OLE DB Provider for Indexing Service | Any | MSIDXS | Indexing Service catalog name | - | - | - |
| Microsoft Excel Spreadsheet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path name of Excel file | - | Excel 5.0 | - |
| IBM DB2 Database | Microsoft OLE DB Provider for DB2 | Any | DB2OLEDB | - | - | See Microsoft OLE DB Provider for DB2 documentation. | Catalog name of DB2 database |
http://support.microsoft.com/kb/280102
