See Also: Main_Page - Database Administration

Accessing Other Databases from SQL Server

Distributed queries provide a way to query data in distinct data sources and combine the output of the results. Distributed queries could be useful if you need to access distributed data occasionally, however, if you need constant access to the data in different sources you may wish to consider combining these data in one form of storage, like using DTS or SSIS. For this tutorial, though, we're going to focus on just querying the databases.

SQL Server supports distributed queries through Linked Servers. You can register data sources with SQL Server (through enterprise manager or through Transact-SQL) as Linked Servers. After you have set up a data source external to SQL Server as a Linked Server, you can run so called pass-through queries against registered Linked Servers.

OpenQuery: Moving a Lot of Data from Microsoft Access to SQL Server

You can use either OPENQUERY or OPENROWSET. Here is the OPENQUERY syntax:

OPENQUERY(linked_server, 'query')

Where "linked_server" represents the name of the Linked Server specified when you set it up. Here is how you would setup a Linked Server with Transact SQL:

EXEC sp_addlinkedserver 'AccessDB',
       'Access', -- this could be any valid identifier 
      'Microsoft.Jet.OLEDB.4.0',
       'C:\DTSBook\Data\MyMdb.mdb'  -- this is the full path of the Access mdb file 

To setup a linked server in Enterprise Manager, open the Security Folder under the host server, then right click on "linked servers" and choose "New Linked Server". You will get a dialog box where you can specify the linked server name, provider name, and connection string properties.

You will have to provide connection string and security information with this syntax. For example, we have setup an MS Access database called MyMdb as a linked server called AccessDB. To query the Sales table in this database we could use following syntax:

SELECT *  FROM OPENQUERY(AccessDB, 'SELECT * FROM Sales')   
GO

OpenRowset: One-Time Queries from Access to SQL Server

If I had not set this database as a linked server, I could use alternative syntax to get the same resultset:

SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\DTSBook\database\MyMdb.mdb';'admin';'', Sales)

In general, you would use OPENROWSET only when you need to query remote data source once (or infrequently). If you need to repeatedly access remote data and you don't wish to import it into SQL Server, you may wish to setup a Linked Server.

Remote Stored Procedure Calls

Remote Stored Procedure calls, commonly referred to as RPC (Remote Procedure Call) are leftover from previous versions of SQL Server. It is no longer recommended to included Remote Stored Procedures in any new code. However, you might run across some RPC while supporting legacy applications. RPC is simply SQL Server's ability to execute stored procedures on a remote server. To use RPC, you will have to setup participating servers as remote servers at the local server. However, unlike linked servers, you cannot query remote server tables. You can only execute stored procedures that are sent to the remote server. Once the remote server processes the procedure it will return the results to the calling server.

In order to use RPC, remote access option has to be set to 1 on both servers. This can be accomplished by using Enterprise Manager or SP_CONFIGURE system stored procedure.

EXECUTE sp_configure 'remote access', 1  
RECONFIGURE  
GO

To register a remote server use SP_ADDSERVER or Enterprise Manager. Once both servers participating in RPC have been setup as remote servers for the other server, you can use SP_ADDREMOTELOGIN to allow login from one server to execute remote stored procedures on the other server. Enterprise Manager lets you specify the remote login while registering the remote server.