Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Uncovering Linked Servers

By Brian Knight,

A little known feature that SQL Server offers is linked servers. Linked servers give the developer the option to use distributed queries. Distributed queries allow you to have your inventory data on DB2 and your accounting data in SQL Server and query them as if they were the same database. SQL Server allows you to execute these queries on any OLE DB compliant data source. This article will show you how to setup and query a linked server.

About Linked Servers

Linked servers are a decendent of remote servers. Remote servers were mostly used to execute replication stored procedures in SQL Server. It is recommended that you do not use remote servers in SQL Server 7.0+. With linked servers, you establish a connection in SQL Server to a remote OLE DB provider. Then, the SQL Server clients connect to the linked server and then, SQL Server connects to the remote provider on behalf of the client. The linked server acts much like a middleman, taking an order passing it to the source, then back to the consumer.

Linked servers are especially handy in a thin-client environment. Establishing connections to servers such as DB2 can be tricky and linked servers minimize the complexity since you only need to do it once. Clients will not need to have an OLE DB provider for DB2 on their workstations for example. They will only need to have the standard SQL Server drivers.

Setting Up a Linked Server

You are probably at a location right now where you can't do many interesting heterogeneous queries. For that reason, the examples that I will use will show you how to link to another SQL Server. I will also explain briefly connecting to DB2 and Oracle.

You can add a linked server either in Enterprise Manager or script it in T-SQL. We will cover the T-SQL method in a later article. In Enterprise Manager, simply expand the Security group, then click your right mouse button on Linked Servers.

In the above example, I've setup a linked server named SQLLINK that will connect to my SQL Server named BKNIGHT. If you'd like to follow this example and don't have another SQL Server to experiment with, simply make the datasource your local SQL Server.

Option Required? Description
Linked server YesThe name you wish to refer to your linked server as. This does not have to be the name of the actual server. For example, my server name is BKNIGHT but I’m using the linked server name of SQLLINK.
Provider nameYesThe type of OLE DB or ODBC provider that you will be using to connect to your remote server. As you can imagine, the range of providers can range from DB2 to Access (JET). Any custom providers will use the OLE DB Provider for ODBC driver.
Product nameNoThis option is simply metadata for your own use. This is the friendly name for the provider name that you selected. For example, if you selected the OLE DB Provider for SQL Server, you could type here SQLServer 7.0.
Data sourceNoThe name of the data source that is translated by the provider. This option’s definition is different depending on what provider is selected. For example, for SQL Server and Oracle databases, you would put the database server name you wish to connect to. For Access, you would put the fully qualified path to the .MDB file document. In ODBC providers, this would be the name of the DSN.
Provider stringNoAny optional connection string required by the OLE DB provider. This is not required for SQL Server connections. Some ODBC connections however, will require this option.
Location No The location of the data source translated by the provider. This option is not required when connecting to SQL Servers or Oracle Servers. Location is passed to the OLE DB provider as the DBPROP_INIT_LOCATION property to initialize the provider.

There are also some important checkboxes below the core options. The most important of these is the Collation Compatible option. When this option is selected, SQL Server assumes that the source and destination servers are both operating on the same collation (sort order and language). If this is selected, SQL Server will pass the query to the provider and let the provider execute the query without SQL Server pulling it back for inspection. This "inspection" can slow down performance substantially. The data access option allows you to access the data on the linked server.

The security tab has some mandatory information needed by the linked server to connect to the provider. I will go into this deeply in a following article. For the time being, map an ID that has access to the Northwind database. Once you click OK, you're done. The bad news is that SQL Server doesn't make it easy to edit a linked server after you've added one. To change connection options, you have to drop and recreate a linked server. You can only change the checkboxes we mentioned earlier and the security.

Querying a Linked Server

Now that you have a linked server added, you can begin querying it. You have three ways to linked server. The easiest method, which works very well with SQL Server is to use a four part qualifier to your queries as shown below. This way of querying your linked server doesn't work well against some heterogenous datasources such as DB2 however.
SELECT *
	FROM
	SQLLINK.NORTHWIND.DBO.CATEGORIES
Now the fun begins. With a linked server you can perform joins as if the linked server was local. The example below goes back to our orginal scenario where you have inventory in one location and the order system in another.
SELECT lnw.CompanyName, rnw.OrderID, rnw.OrderDate, rnw.Freight
FROM   SQLLINK.Northwind.dbo.orders rnw, Northwind..Customers lnw
WHERE  lnw.CustomerID = rnw.CustomerID 
AND    rnw.shipcountry = 'USA'
AND    rnw.OrderDate > ' 04 / 22 / 1998'
You can also use this technology to make a few databases look like one using the UNION ALL command. The below query will gather all categories from two SQL Servers and merge the returned results into one recordset.
SELECT *
	FROM
	SQLLINK.NORTHWIND.DBO.CATEGORIES
UNION ALL
Select * 
	FROM
	Northwind..Categories
The other method of querying a linked server is to use the OPENQUERY command. This command is perfect for heterogeneous databases because it executes the requested query on the remote system, not the SQL Server that holds the linked server. I've had many queries that just won't work with the four part identifier that work fine with the OPENQUERY command. With the OPENQUERY command, you will tell SQL Server that you want to select all the records returned from the query in quotes. The first required piece of your syntax is the linked server name followed by the actual query. Notice that we use single quotes around the conditional piece of the query.
SELECT * 
FROM
OPENQUERY(SQLLINK,
	"SELECT * FROM northwind..Categories where CategoryName Like 'Sea%'")
The final way to query a remote provider is to use the OPENROWSET command. The OPENROWSET doesn't require that you have a linked server setup at all. It does use the technology, but it creates it at runtime. It's a handy way to dynamically setup where you wish to connect to. The OPENROWSET command also operates the same way that an OPENQUERY command operates by executing all queries on the destination server. The below query shows you how to write such a command.
SELECT a.orderid, a.shippeddate , b.companyname
FROM Northwind.dbo.orders AS a INNER JOIN
	OPENROWSET('SQLOLEDB','BKNIGHT';'sa';'',
	"SELECT Customerid, CompanyName FROM northwind.dbo.customers 
	WHERE country = 'USA' and Region = 'AK' ORDER BY country") 
	as b
	on a.CustomerID=b.CustomerID
What's also nice about the above example, is it's also an example of how to perform joins using the OPENROWSET or OPENQUERY commands. Although the OPENROWSET is a neat command, I wouldn't recommend using the OPENROWSET command unless you absolutely can justify a need. It's much nicer to have a centralized location where you can configure your linked server. When using this command you will have to go through your code and hunt down all the locations of OPENROWSET if a change to the server occurs.

Next week we will cover more advanced options in linked servers and how to script them.

Total article views: 21046 | Views in the last 30 days: 44
 
Related Articles
FORUM

select query

select query

FORUM

Select query

Select query

FORUM

Named Pipes Provider: Could not open a connection to SQL Server [2].

Named Pipes Provider: Could not open a connection to SQL Server [2].

FORUM

Linked Server Oracle Unable to execute select QUERY

Cannot execute select query in linked server whereas Insert query works fine

FORUM

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - C...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones