Let us take a closer look at the SQL server openquery and its functions with the assistance of our MSSQL support services at Bobcares.

Openquery

The OPENQUERY function is a tool to do an ad hoc distributed query on a distant data source via the connected server. There are several methods for querying the distant data source. It is an effective tool to perform and conduct pass-through queries that perform INSERT, UPDATE, and DELETE statements on the tables of the database setup on the associated server.         

Openquery function features for SQL server management

1. The OPENQUERY function is a quick way to get data from a remote server. If we need to query the distant server frequently, we should utilize the connected server instead.
2. We cannot use the Parameters in the OPENQUERY function, nor can use them to perform the extended sored process on the associated server.
3. The SQL Server sends the query to a remote server when we use the OPENQUERY to retrieve distant data. The remote server handles processes like query processing and execution plan generation.
4. The OPENQUERY is faster than the linked server because when we use the linked server, the SQL Server breaks the query into local and remote queries. Local queries run on the local server, whereas remote queries; sent to the remote server. The SQL server combines the result set and returns the final result set.

The syntax of the OPENQUERY function is as follows:   

SELECT * FROM OPENQUERY (linkedServer, ‘Query’)

Following the FROM clause, the OPENQUERY function is called. In terms of syntax,  

1. LinkedServer: Enter the name of the connected server where we want to execute the query.
2. Query: Enter the T-SQL query that will be run on the connected server. The query string can elongate to 8KB long.

Environment Setup

To show the use of the OPENQUERY, SQL server 2019 and PostgreSQL13 are installed on the computer. Two connected servers are established. The first connected server establishes a connection to the distant SQL Server instance, known as Nisarg-PCSQL01.    

Access the PostgreSQL database via the second connected server, PostgreSQL30. The PostgreSQL30 DSN (Data source name) configuration using the ODBC driver. The DSN is used by the server to access the UKLandRegistery database. Run the following query to get a list of connected servers.      

use master  go  select srvid,srvstatus, srvname,srvproduct,datasource,schemadate,catalog from sys.sysservers

 

Examples

A. Executing an UPDATE pass-through query

The example that follows employs a pass-through UPDATE openquery against the connected sql server defined in example A.

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')   
SET name = 'ADifferentName';  

B. 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');  

C. Executing a DELETE pass-through openquery

To remove the record added in example B, the following example employs a pass-through DELETE query.

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');  

D. Executing a SELECT pass-through query

 The row in example B is selected using a pass-through SELECT query in the next example.          

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');  

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up we have now seen more on the sql server openquery with the support of our tech support team.