sp_addlinkedserver (Transact-SQL)

By Code Collector

Create a SQL Server linked server with sp_addlinkedserver. Syntax and code samples shown below.

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ]
     [ , [ @location= ] 'location' ]
     [ , [ @provstr= ] 'provider_string' ]
     [ , [ @catalog= ] 'catalog' ]
USE master;
GO
EXEC sp_addlinkedserver
   'SEATTLESales',
   N'SQL Server'
GO
EXEC sp_addlinkedserver  
   @server='S1_instance1',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='S1\instance1'
EXEC sp_addlinkedserver
   @server = 'SEATTLE Mktg',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
EXEC sp_addlinkedserver
   @server = 'SEATTLE Mktg',
   @provider = Microsoft.ACE.OLEDB.12.0',
   @srvproduct = 'OLE DB Provider for ACE',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
GO
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO
EXEC sp_addlinkedserver
   @server = 'SEATTLE Payroll',
   @srvproduct = '',
   @provider = 'MSDASQL',
   @datasrc = 'LocalServer'
GO
EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'
GO
SELECT *
   FROM ExcelSource...SalesData
GO
EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0'
EXEC sp_addlinkedserver @server = N'ExcelDataSource',
@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\DataFolder\People.xlsx',
@provstr='EXCEL 12.0' ;

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a four-part name.
SELECT *
FROM txtsrv...[file1#txt]
EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;'
sp_addlinkedserver (Transact-SQL)  (8328 Views)