Linked Servers in SQL Server

This article helps you to understand the concept of Linked Servers in SQL Server.

 
I will show with an example, how to connect the two different databases using Linked Servers in SQL Server 2008, 2012 step by step.

What is Linked Servers or Database Links?

Don't be confused by the two terms; both are the same. In SQL Server it is called a Linked Server whereas in Oracle it's DBLinks (Database Links).

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers. 

It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers. 

The remote servers can be SQL Server, Oracle etc. which means those databases tht support OLE DB can be used for linking servers.

Here the list of OLE DB Providers tested with SQL Server.

After setting up the Linked Servers we can easily access the other server tables, procedures etc. 

Once the connection is established, we can start with the CRUD operations. 

The advantage is about security; its works on Windows as well as SQL Server Authentications.

How to find information about existing Linked Servers?

We can get Linked Server basic information by executing the following stored procedure created in the master database or default system.

exec sp_linkedservers

output as below

Or using select * from sys.servers will return more information about servers.

You can get more columns and other information from this msdn article.

How to setup Linked Servers?

This can be done in two ways.

1. Using Transact-SQL

Using the following syntax we can create a Linked Server.



Example:



This will create a Linked Server; we can view this from Management Studio.


Or by executing the following stored procedure, exec sp_linkedservers.



Now if want to see an entire SQL Script behind creation of a Linked Server, then from Management Studio right-click on:
LSNorthwind and Script Linked Server as -> CREATE To -> New Query editor window.

2. SQL Server Management Studio

Now by using SQL Server Management Studio we can create Linked Servers. I will show how to create connectivity with Oracle Database. 

First of all we need to open telnet ports of Oracle Database on SQL Server. This will help to create communication between two different servers. 

If an Oracle OLE DB provider not installed, then download from this location and install. Download.

Otherwise if the oracle client is already installed, then the driver is also updated.

Step 1:

Open SQL Server Management Studio; go to Server Objects -> Linked Server.

Under Linked Server node we have Providers node that already having installed provides and that mapped to SQL Server. 

Now right click on Linked Server node and click on New Linked Server which will open a new window for setup as below.



The new window contains following listing:

• Linked Server -> Its Linked Server name which needs to be created.
• Server Type -> It can be SQL Server or other data sources.
• Provider -> Will list all the installed providers.
• Product Name, e.g. Oracle, SQL Server.
• Data source, e.g. Oracle Database data source or other provider data source.
• Provider String or connection string, optional
• Catalog is database name, optional

Step 2:

Select Oracle Provider for OLE DB from Provider list and enter Data source information. This can be your Oracle database server IP with port name. 

E.g. Data source -> 1.1.1.1:1521 or data source name. Here we can provide the whole connection string with user name and password and avoid 

updates in the Security tab. Enter Database name under Catalog entry.

Step 3:

Or, instead of updating the provider string under th General tab, we can provide login credential details also under the Security tab at the top left corner.

Step 4:

Under the Server Options tab we can update Connection Timeout, query execution timeout etc.

Now click on OK; this validates the entries and the Linked Server is created. If validation fails then an error widow will be opened with error information.

Note: How to find connection string or data source information, refer to the following article.

Find Database Connection and Generate Connection String using Visual Studio

How to do CRUD operations using Linked Server?

By using the OPENQUERY function we can execute the specified pass-through queries on the specified Linked Server and return the output.

Sample Select Syntax:

SELECT * FROM OPENQUERY(LSNorthwind, 'select * from dbo.Categories')

Call a function or Stored Procedure:

SELECT * FROM OPENQUERY(LSNorthwind, 'EXEC [dbo].[CustOrdersOrders] VINET')

Insert records:

insert OPENQUERY(LSNorthwind, 'select CategoryName, Description from dbo.Categories')
select 'Testing', 'Testing'

To insert records we need to first select those columns to which records are to be inserted and pass the values through the select statement.

Update records:

update OPENQUERY(LSNorthwind, 'select CategoryName from dbo.Categories where CategoryID=10')
set CategoryName = 'New Test'

To update records we need to first select those columns to which records are to be updated and pass the values through the select statement with column names. We can have multiple column names separated with a comma.

Delete records:

delete OPENQUERY(LSNorthwind, 'select * from dbo.Categories where CategoryID in (9, 10, 11)')

We just need to select those records to be deleted and execute the delete statement.

Using Dynamic Queries:

Drop a Linked Server:

EXEC sp_dropserver 'LSNorthwind'

Conclusion

This article was about creating Linked Servers in SQL Server 2008, 2012 and samples shows setting up connection with Oracle Server by 

selecting Oracle OLE DB provider with login credentials. Hope this article helped, please rate the article and post your comments.

Post your queries to our Forum sections.

Thank You!