Discussing Execute(SQL) At LinkedServer

This article details the use of the “Execute AT LinkedServer” clause. It’s best in some ways when you are trying to run dynamic, complex queries across heterogeneous data sources. There are many instances where OpenQuery/OpenRowSet and four-part qualifier calling might not work in complex SQL design. The limitations of linked servers will explode when are trying to manipulate data and write complex queries with heterogeneous data sources.

{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + ...n ] 
        [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] 
    )  
    [ AS { LOGIN | USER } = ' name ' ] 
    [ AT linked_server_name ] 
[;]

DECLARE @Script nvarchar(max) =
    N'
    <dynamic sql script>
    ';
EXECUTE (@Script) AT <linked_server_name>
INSERT <table> (columns)
EXECUTE (@Script) AT <linked server>;

The SQL could be a single query or a sequence of statements, dynamic SQL, or be entirely static. The linked server could be an instance of SQL, Oracle, DB2, etc. The use of Openquery, Openrowset, and four-part calling stored procedure might work in simple cases. When you are working with distributed queries with heterogeneous data sources, the use of EXECUTE … AT LinkedServer works best. The SQL Server extends the EXECUTE statement so that it can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set.

Known Errors

Error 1: The Object Has No Columns or the Current User Does Not Have Permissions on That Object

"Cannot process the object "<query text>."The OLE DB provider "<provider>" for linked Server "<server>" indicates that either the object has no columns or the current user does not have permissions on that object."

The OpenQuery and OpenRowset are often best used with only a single SELECT statement. By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;’ to the SQL string, it will ignore validating the output format and it will return the data as-is. The OPENROWSET command operates the same as the OPENQUERY command but OpenRowSet provides a flexibility in creating dynamic connections.

Error 2: Unable to Begin Distributed Transaction

“The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “X” was unable to begin a distributed transaction.”

To re-enable the RCP commands for the linked server:

Calling remote SP with four-part qualifier name.

Error 3: The Transaction Manager Has Disabled Its Support for Remote/Network Transactions

In my case, the remote server was configured with an IBM I-Series driver.

•  Open “Component Services”

Control Panel > Administrative tools > Component Services

•  In Component Servies, right click “My Computer” and select “Properties”

Console Root > Component Services > Computers > My Computer

•  Select the “MSDTC” tab (Select appropriate MSDTC if it’s clustered) , and click “Security Configuration” in the “Transaction Configuration”

Enable Allow inbound and Allow outbound.

•  Restart the DTC service (should do so automatically).

The below example shows the execution of a distributed query executed on the remote server that fetches the data and writes it an another remote SQL instance.

Conclusion

You can safely use Linked Servers in a production setting, but do your research and test before settling on a solution.

OPENQUERY/OPENROWSET guarantees the SQL will execute on the remote server and only bring back the results from that query to the local server.In many cases, this can be used to simple SQL statements. When the SQL is getting complex and needs to query heterogeneous data sources, it may get little cumbersome. EXEC() AT is similar to OPENQUERY/OPENQUERY in that the static SQL/dynamic SQL will always execute on the remote server except that you cannot use the results locally within a JOIN but you can use them, however, to INSERT them into a local table. Also, EXEC() AT allows you to provide SQL in a variable, whereas OPENQUERY will not accept a variable, which many times forces the use of dynamic SQL.

As usual, any feedback is welcome, and I hope that this article was helpful to you!