Querying Linked Server with OPENQUERY

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a “four-part naming” convention (SQLserver.SQLdatabase.dbo.SQLfunction), you may receive error message.

 

Linked Server Error

The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. That’s why error message indicates that the syntax of a Transact-SQL statement is incorrect.  

To work around this problem, use the OPENQUERY function instead of the four-part naming convention.

What 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.

Principles

OPENQUERY ( linked_server ,'query' )

Arguments

linked_server: the name of the linked server.

 query ‘: string executed in the linked server. The maximum length of the string is 8 KB.

Note

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name

Permissions

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

Usage

    • pass-through UPDATE query against the linked server
UPDATE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE id = 101') SET city = 'Melbourne';
    • pass-through INSERT query against the linked server 
INSERT OPENQUERY (Link_Server, 'SELECT address FROM person.addresses ') VALUES ('NewAddress');
    • pass-through DELETE query 
DELETE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE address = ''NewAddress''');
    • SELECT pass-through query
SELECT * FROM OPENQUERY (Link_Server, 'SELECT SQLdatabase.dbo.SQLfunction(functionParam1, functionParam2)');



Leave a Reply

Your email address will not be published. Required fields are marked *