SQL Linked Servers

Adding Linked Servers

To add a linked server in SQL Server 2005, you can use the sp_addlinkedserver command. The syntax is:

sp_addlinkedserver 'SERVERNAME','PRODUCT_NAME','PROVIDER','DATA_SOURCE','LOCATION','PROVIDER_STRING','CATALOG'
  • SERVERNAME - This is the name of the linked server that you will refer to.
  • PRODUCT_NAME - Usually just a text string that has no effect on the connection.
  • PROVIDER - See chart below.
  • DATA_SOURCE - Network name for server or path to file or DSN of data source.
  • LOCATION - A string describing your source.
  • PROVIDER_STRING - See chart below.
  • CATAGLOG - Usually an optional database name.

 

Remote OLE DB data source

OLE DB provider

product_name

provider_name

data_source

location

provider_string

catalog

SQL Server

Microsoft SQL Server Native Client OLE DB Provider

SQL Server (default)

 

 

 

 

 

SQL Server

Microsoft SQL Server Native Client OLE DB Provider

 

SQLNCLI

Network name of SQL Server (for default instance)

 

 

Database name (optional)

SQL Server

Microsoft SQL Server Native Client OLE DB Provider

 

SQLNCLI

servername\instancename (for specific instance)

 

 

Database name (optional)

Oracle

Microsoft OLE DB Provider for Oracle

Any

MSDAORA

SQL*Net alias for Oracle database

 

 

 

Oracle, version 8 and later

Oracle Provider for OLE DB

Any

OraOLEDB.Oracle

Alias for the Oracle database

 

 

 

Access/Jet

Microsoft OLE DB Provider for Jet

Any

Microsoft.Jet.OLEDB.4.0

Full path 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 of Excel file

 

Excel 5.0 or Excel 8.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

Examples

Another SQL Server

USE master
GO
EXEC sp_addlinkedserver
    'plab2catsg02',
    N'SQL Server'
GO

Excel Linked Server

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\plab2catsg04\Restructure\Restructures.xls',
   NULL,
   'Excel 8.0'
GO

Textfile Linked Server

 EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO 

Oracle Linked Server

EXEC sp_addlinkedserver 
  'OracleLinkedServer', 'Oracle', 
  'MSDAORA', 'OracleServer' 

EXEC sp_addlinkedsrvlogin ' 
  OracleLinkedServer ', false, 
  'SQLuser', 'OracleUser', 
  'OraclePwd'

Listing Linked Servers

You can use the sp_linkedservers command to provide a list of linked servers.

Dropping Linked Servers

To drop a linked server, it is as simple as executing the following command:

sp_dropserver 'LINKEDSERVERNAME' 'LOGINS'

Only sysadmins or setupadmins can execute this. Also, this cannot be executed inside a user-defined transaction.

}