Rowset Function – OPENQUERY

OPENQUERY is used to execute the specified query with the help of mentioned Linked server. Linked Server can reference any OLEDB Data source. OPENQUERY is referenced in the FROM Clause as if it were a table name. INSERT DELETE and UPDATE operations can be executed through OPENQUERY function. Extended stored procedures cannot be executed through OPENQUERY function. But it can be directly executed through Linked Server. It returns only the first record even though query returns multiple result sets.

Syntax

          OPENQUERY (<Linked Server Name>, ‘Query’)

<Linked Server Name>  -- It indicates the name of the Linked server.

<Query>                     -- It specifies the query to be executed through Linked Server. Maximum length of query string is 8KB.

Pre-Configuration Setup

In order to use OPENQUERY Rowset function, the following pre-configurations have to be set.

1.  Make sure that specific Linked server has already been defined on your SQL Server. Linked server can reference any OLEDB Data source.

2. Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

          SP_CONFIGURE 'show advanced options', 1

          RECONFIGURE;

          EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

          RECONFIGURE;

Examples

LServ_Orcl is the defined linked server name which refers OLEDB data source.

Below query, inserts a record into EMPDB.Designation_Master table.

INSERT OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Designation_Master') VALUES ('ARCHITECT');

This query updates a record in EMPDB.Employee_List table for the Emp_ID 9.

UPDATE OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Employee_List WHERE Emp_ID = 9') SET Designation = 'ARCHITECT';

This query deletes a record from EMPDB.Destination_Master table.

DELETE OPENQUERY (LServ_Orcl, 'SELECT Designation FROM EMPDB.Designation_Master WHERE Designation = ''ARCHITECT''');