Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 240 of 361 Next >

SQL Server I/O: Remote Queries

Last updated Mar 28, 2003.

If you’re looking for more up-to-date information on this topic, please visit our SQL Server article, podcast, and store pages.

Sometimes the data you're after isn't in your database. Sometimes it isn't on your server. Sometimes it isn't even on SQL Server at all.

In these situations, you could call on the application developer to write individual queries against other databases, servers or platforms. The developer could take that data and put it together on the screen for the user.

But if you're a DBA, you really don't want to give that kind of work to the developer. You want to deliver a single solution for the developer, regardless of the location of the data.

The good news is that you've got options. I'll show you how to get data from other databases, servers and sources, and even how to join them together. The bad news is that the access comes with a price – and that price can be really poor performance.

The first possible situation is that the data you're after might not be in your own database. This is a simple fix, and only involves using three part names. The three part name of a database object has this format:

Database.owner.object

To demonstrate, I've created another database with one table on my system like this:

CREATE DATABASE pubs2
GO

USE pubs2
GO

CREATE TABLE authorsinfo 
	(
	au_id varchar(15) 
	, foodgroup varchar(50)
	)
GO

INSERT INTO authorsinfo
VALUES 	(
	'172-32-1176'
	, 'Chocolate'
	)
GO

I also entered one value, which combines one of my author's id fields with a favorite food group (chocolate, what else?).

If I want to see data from those two databases, all I have to do is join them with a three part name:

SELECT a.au_fname + ' ' + a.au_lname as 'Name', b.foodgroup as 'Favorite Food Group'
FROM pubs.dbo.authors a	
	INNER JOIN pubs2.dbo.authorsinfo b
	ON a.au_id = b.au_id
GO
Name               Favorite Food Group        
------------------------------------------------------------- -------------------------------------------------- 
Johnson White             Chocolate

(1 row(s) affected)

You can also create views with these types of statements. In fact, in one application I had I kept OLTP data in one database, and created an entirely different database with nothing but views in it. I did this to enable a simple security mechanism, and to hide the complexity of the data that had a requirement to be case-sensitive. The views were stored in a non-case sensitive database, allowing the users to access it without running into problems with the column headings.

If the data is stored in another server entirely, you can access it using a remote server and a stored procedure. Remote servers need to be on a fairly fast connection, or your queries will really pay the price; but if the application calls for infrequently accessed data, it can be a lifesaver. You can set up a remote server graphically in Enterprise Manager, or use the sp_addlinkedserver stored procedure. You set up the first server, and then perform the same procedure on the second server. The final step is to set up a user from the first server on the second, which provides your security. Books Online shows the entire process in code:

First Server:

EXEC sp_addlinkedserver ServerName1, N'SQL Server'
EXEC sp_addlinkedserver ServerName2
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO

Stop and restart the first SQL Server, and then run this code on the second server:

-- The example shows how to set up access for a login 'sa'
-- from ServerName1 on ServerName2.
EXEC sp_addlinkedserver ServerName2, local
EXEC sp_addlinkedserver ServerName1
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO

-- Assumes that the login 'sa' in ServerName2 and ServerName1
-- have the same password.
EXEC sp_addremotelogin ServerName1, sa, sa
GO

On your first server, you now can access stored procedures on the second. The query runs on the second server and returns only the results to the first.

You can also use a linked server to access another SQL Server.

The real power here is that you can access data that isn't in a SQL Server at all. You can use Enterprise Manager or a few stored procedures to create the linked server.

Here's an example of using a linked server to get data from a Microsoft Access database. This stored procedure sets up the linked server (this only has to be done once) I call "Writing," and then queries the Notes field from a table in the Access database called tblProjects:

EXEC sp_addlinkedserver
@server = 'Writing'
, @Srvproduct = 'Access'
, @Provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = 'c:\temp\writing.mdb'
GO

SELECT Notes
FROM Writing...tblProjects
GO

You can find all the @Provider strings in Books Online under the subject of sp_addlinkedserver.

If you'd like to access Access (I love saying that) without setting up a linked server, there are a couple of remote query methods you can use. Here's an example of using the OPENROWSET command, which you can treat just like a table:

SELECT * FROM
OPENROWSET(
'Microsoft.Jet.OLEDB.4.0'
, 'c:\temp\writing.mdb'; 'admin';
, 'SELECT Notes FROM tblProjects')

The format for this command depends on the provider you're talking to, but all OLE DB formats are supported, including ODBC connections. In the third line, you can see the provider string, and in the fourth line you enter the "database" name and credentials for login. The final line has the remote SELECT that creates the dataset the outer SELECT uses as a table.

In addition to OPENROWSET, you can also use OPENDATASOURCE as well. Here's an example of using that to access an ODBC connected datasource called ProdServer:

SELECT *
FROM  OPENDATASOURCE(
   'SQLOLEDB',
   'Data Source=ProdServer;User ID=Buck;Password=passwd'
   ).pubs.dbo.authors

You can join the results of these queries to local tables, enabling a really distributed architecture.

All this flexibility comes with a price. Depending on several factors these queries can have really awful performance. If the link is slow, the query processor is busy, or the connection model is poor, they can be quite slow.

On the other hand, it's really useful to be able to access data from various sources without having to resort to multiple client installations or using code.

One more thing: security is also a concern. To properly implement a good security plan, use linked servers. They provide greater flexibility in security options.