Openquery (TRANSACT-SQL)

ArticleDirectory

• A. Execute select to pass the query
• B. Execute update transfer Query
• C. Execute insert to pass the query
• D. Execute Delete to pass the query

Executes the specified transfer query on the given linked server. This server is an ole db data source. Openquery can be referenced in the from clause of the query, as if it is a table name. Openquery can also be referenced as the target table of the insert, update, or delete statement. However, this depends on the functions of the ole db access interface. Although a query may return multiple result sets, openquery returns only the first result set.

Transact-SQL syntax conventions

Syntax

 
Openquery (pai_server, 'query ')
 

Parameters

Pai_server

The identifier of the linked server name.

' Query '

Query string executed on the linked server. The maximum length of this string is 8 KB.

Note

Openquery does not accept its parameter variables.

Openquery cannot be used to execute extended stored procedures on linked servers. However, you can run the extended stored procedure on the linked server by using the four-part name. For example:

 
Exec seattlesales. master. DBO. xp_msver
 

Permission

Any user can execute openquery. The permissions used to connect to the remote server are obtained from the settings defined for the linked server.

Example A. Execute select to pass the query

The following example uses "Microsoft Access interface for Oracle" to createOraclesvr. Then, this example is used for the linked serverSelectPass query.

Note:
This example assumes thatOrcldbThe alias of the Oracle database.

 
Exec sp_add1_server 'oraclesvr ',
'Oracle 7.3 ',
'Msdaora ',
'Orcldb ';
Go
Select *
From openquery (oraclesvr, 'select name, ID from Joe. Titles ');
Go
 

B. Execute update transfer Query

The following example is used for the linked server created in Example.UpdatePass query.

 
Update openquery (oraclesvr, 'select name from Joe. Titles where id = 100 ')
Set Name = 'adifferentname ';
 

C. Execute insert to pass the query

The following example is used for the linked server created in Example.InsertPass query.

 
Insert openquery (oraclesvr, 'select name from Joe. Titles ')
Values ('newtitle ');
 

D. Execute Delete to pass the query

The following example usesDeletePass query to delete the row inserted in example c.

 
Delete openquery (oraclesvr, 'select name from Joe. Titles where name = ''newtitle ''');
 

See

Delete (TRANSACT-SQL)
From (TRANSACT-SQL)
Insert (TRANSACT-SQL)
OpenDataSource (TRANSACT-SQL)
OpenRowSet (TRANSACT-SQL)
Dataset functions (TRANSACT-SQL)
Select (TRANSACT-SQL)
Sp_addmediaserver (TRANSACT-SQL)
Sp_serveroption (TRANSACT-SQL)
Update (TRANSACT-SQL)
Where (TRANSACT-SQL)
Other resources

Distributed Query