OPENQUERY in SQL Server

Recently, a colleague of mine asked me to explain the following question (one of the exam questions of 70-431):
 

Your application must access data that is located on two SQL Server 2005 computers. One of these servers is named SQL1 and the other is SQL2. You have permissions to create a stored procedure on SQL1 to support your application. However, on SQL2 you only have permissions to select data.

 

You write the stored procedure on SQL1. The stored procedure accesses SQL2 by using the OPENQUERY Transact-SQL statement. However, the query fails when executed.

 

You need to troubleshoot the cause of the error. What should you do?

 

A. Join the two servers by using the four-part syntax of server.database.schema.table.

B. Reference SQL2 by using an alias.

C. Add SQL2 as a remote server to SQL1.

D. Add SQL2 as a linked server to SQL1.
 
We were discussing on why the answer is D. The keyword here is ‘OPENQUERY’. OPENQUERY executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.
 

The syntax of OPENQUERY is
 
OPENQUERY ( linked_server ,’query’ )
 
The arguments of OPENQUERY is

1. linked_server – Is an identifier representing the name of the linked server.
2. ‘ query ‘ – Is the query string executed in the linked server. The maximum length of the string is 8 KB.
 
For example,
 
A. Executing a SELECT pass-through query
The following example creates a linked server named OracleSvr against an Oracle database by using the Microsoft OLE DB Provider for Oracle. Then, this example uses a pass-through SELECT query against this linked server.
 
EXEC sp_addlinkedserver ‘OracleSvr’,
   ‘Oracle 7.3’,
   ‘MSDAORA’,
   ‘ORCLDB’
GO
SELECT *
FROM OPENQUERY(OracleSvr, ‘SELECT name, id FROM joe.titles’)
GO
 
B. Executing an UPDATE pass-through query
 
The following example uses a pass-through UPDATE query against the linked server created in example A.
 
UPDATE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE id = 101’)
SET name = ‘ADifferentName’;
 
C. Executing an INSERT pass-through query
 
The following example uses a pass-through INSERT query against the linked server created in example A.
 
INSERT OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles’)
VALUES (‘NewTitle’);
 
D. Executing a DELETE pass-through query
The following example uses a pass-through DELETE query to delete the row inserted in example C.
 
DELETE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE name = ”NewTitle”’);

Advertisements