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