Linking Servers

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

  • Remote server access.

  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

  • The ability to address diverse data sources similarly.

A linked server definition specifies the following objects:

  • An OLE DB provider

  • An OLE DB data source

An OLE DB provider is a DLL that manages and interacts with a specific data source. An OLE DB data source identifies the specific database that can be accessed through OLE DB. Although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats. These include text files, spreadsheet data, and the results of full-text content searches.

The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI10) is the official OLE DB provider for SQL Server.

Note Note

SQL Server distributed queries are designed to work with any OLE DB provider that implements the required OLE DB interfaces. However, SQL Server has been tested against only the SQL Server Native Client OLE DB Provider and certain other providers. For more information, see OLE DB Providers Tested with SQL Server.

The following illustration shows the basics of a linked server configuration.

Client tier, server tier, and database server tier

Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.

For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.

When a third-party OLE DB provider is used, the account under which the SQL Server service runs must have read and execute permissions for the directory, and all subdirectories, in which the provider is installed.

There is a set of options that control how SQL Server loads and uses OLE DB providers that are specified in the registry. For more information, see Configuring OLE DB Providers for Distributed Queries.

When you are setting up a linked server, register the connection information and data source information with SQL Server. After registered, that data source can be referred to with a single logical name.

You can use stored procedures and catalog views to manage linked server definitions:

  • Create a linked server definition by running sp_addlinkedserver.

  • View information about the linked servers defined in a specific instance of SQL Server by running a query against the sys.servers system catalog views.

  • Delete a linked server definition by running sp_dropserver. You can also use this stored procedure to remove a remote server.

You can also define linked servers by using SQL Server Management Studio. In the Object Explorer, right-click Server Objects, select New, and select Linked Server. You can delete a linked server definition by right-clicking the linked server name and selecting Delete.

When you execute a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name. For more information, see Distributed Queries.

Community Content Add
Annotations FAQ
Create linked server for Excel & MySQL - Steps
Excel: http://sqlwithmanoj.wordpress.com/2010/11/12/query-excel-file-source-through-linked-server/
MySQL: http://sqlwithmanoj.wordpress.com/2010/11/10/linked-server-in-ms-sql-server/
Use Oracle 11g client 11gR2 to create linked servers to remote Oracle servers
Hello,

Use Oracle 11g client 11gR2 to create linked servers to remote Oracle servers instead of using Oracle 11g client 11gR1 (win64_11gR1_client or 11.1). The following article explains some issues you may find using Oracle 11g client 11gR1:

http://www.sqlcoffee.com/Troubleshooting091.htm

Hope this helps.
Regards,

Alberto Morillo
SQLCoffee.com
select data from linked server
--create linked server from sql server to sql server
-- here is sample of create linked server and how retrive data and insert, delete, update

EXEC sp_addlinkedserver  
   @server='BSIRP',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='jayant_PROD_2005'

EXEC sp_addlinkedsrvlogin 'BSIRP', 'True', 'jayant.com\jayant.das', 'Password'

--select Query
SELECT * FROM OPENQUERY (bsirp, 'SELECT * FROM a')
--Insert query
INSERT INTO bsirp.master.dbo.a values (1,'jayant')
--update Query
UPDATE bsirp.master.dbo.a set a=25
--delete query
delete from bsirp.master.dbo.a where a=25
or
DELETE OPENQUERY (bsirp, 'SELECT * FROM a') WHERE a = 25
Thanks & Regards
jayant Dass
jayant.dass@gmail.com
9313406257
Microsoft does not recommend using Linked Servers on SQL Server 2005 or above
I've recently been advised by Microsoft to limit the use of linked servers and that they really do not recommend using them at all.   I find it strange that this is not highlighted in BOL or in any other documentation.   Has anyone else heard the same thing?
Linking server to Salesforce Database - OLEDB
How we can connect to Salesforce database (cloud) via oledb?..
Complex server names
For a query against a linked server named SQL-DES\INSTA use the following syntax in a SELECT:

SELECT * from [SQL-DES\INSTA].database.schema.table


Creating a linked server to DB2
Hello,

Learn how to create a linked server from Microsoft SQL Server 2008 R2 to an IBM DB2 database via ODBC on the following link.

http://www.sqlcoffee.com/Tips0013.htm

Regards,

Alberto Morillo
SQLCoffee.com