Accessing Remote Data  «Prev  Next»

Lesson 8 Using OPENQUERY
Objective Use the OPENQUERY remote data function to access remote data.
The OPENQUERY function is used in very much the same way as the OPENROWSET function is, but it uses a server that is already linked. The OPENQUERY function uses the syntax shown in the following display.

SELECT *
FROM OPENQUERY(as400, '
    SELECT *
    FROM table 
    WHERE column = ''value''
')
    

Using OPENQUERY

OPENQUERY example

In the previous lesson, you used the OPENROWSET function to access the Timesheets table using the parameters shown below:
Table name Timesheets
Server Boston
Login amann
Password amannpwd

You could use OPENQUERY function for the same purpose, as long as the Boston server is already linked, with the following Transact-SQL statement:
SELECT t.*
FROM OPENQUERY('Boston','SELECT * FROM Timesheets') AS t

Notice that the only difference in the way the above query is structured from the OPENROWSET query is that you do not have to specify the connection parameters for the OLE DB data source. You already did that when you created the linked server.
In the next lesson, we will review the information covered in this module.

Distributed data sources pull in data from other SQL Server databases, other SQL Servers, other database platforms (e.g., Microsoft Access, Oracle, Foxpro), or applications (e.g., Excel) using openquery() and other distributed functions.