Accessing Remote Data  «Prev  Next»

Lesson 8 Using OPENQUERY
Objective Use the OPENQUERY remote data function to access remote data.

Use OPENQUERY to Access Remote Data using Function

The OPENQUERY function is still used in SQL Server, even when a server is already linked. In fact, it's the primary way to execute ad-hoc queries on remote data sources accessible through linked servers.
Here's why OPENQUERY remains relevant:
  1. Flexibility: It allows you to directly run any kind of SQL query on the remote server, unlike some linked server options like remote stored procedures that might be limited to specific tasks.
  2. Simplicity: The syntax is straightforward, making it easy to learn and use even for occasional needs.
  3. Power: It can handle complex queries involving joins, aggregations, and other operations on the remote data.
  4. Customization: You can use the results directly in your local query or even modify them using UPDATE, INSERT, or DELETE statements.

However, there are some situations when alternative approaches might be preferable:
  1. Performance: OPENQUERY can introduce overhead compared to optimized remote stored procedures.
  2. Security: It relies on the permissions configured for the linked server, so careful management is crucial.
  3. Complexity: For frequently used tasks, creating and maintaining dedicated stored procedures on the remote server can offer better maintainability and control.

Ultimately, the choice between OPENQUERY and other options depends on your specific needs and priorities. If you need maximum flexibility and simplicity for occasional remote queries, OPENQUERY is still a valuable tool in your SQL Server arsenal.

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''
')

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. 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.
In the next lesson, we will review the information covered in this module.