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 | Yes | The 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 name | Yes | The 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 name | No | This 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 source | No | The 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 string | No | Any 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.
