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	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.

This page was created in 0.00077 seconds

Comments and Questions
Last modified: December 20 2012.