Discussing Execute(SQL) At LinkedServer

Dealing with remote severs and heterogeneous data sources? See how you can link your servers and execute your SQL on them.

· Database Zone · Tutorial
Save
6.67K Views

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.

Introduction

I recently ended up calling a remotely stored procedure (“X”) from a remote machine (“Y”), and the script needed to pull data it stored on a remote server (“Z”). I set up the linked server, and everything was working great. Executing queries to pull the data to my machine was fine, but a problem arose when I needed to execute a stored procedure from the other server.

The three methods I have tried to execute the SP on remote server are:

  • Calling with a four-part naming convention
  • Using OpenQuery and OpenRowSet.
  • Execute At LinkedServer.

The first method was not successful, as there is a dependency on inbound and outbound transactions. I was successful in the execution of the SP using Execute(‘SQL’) AT LinkedServer

Prerequisites

  • Make sure RPC and RPC Out parameters are set to TRUE.
  • MSDTC is enabled to run distributed queries.

Syntax:

Execute a pass-through command against a linked server.

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


Example:

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:

exec sp_serveroption @server='SERVERNAME1′, @optname='rpc', @optvalue='true'

exec sp_serveroption @server='SERVERNAME1', @optname='rpc out', @optvalue='true'

remoteproc1

Calling remote SP with four-part qualifier name.

EXEC ADBSP18.DW_PROD.dbo. [trans_Tracs_Pull] '7/1/2016','7/31/2016'

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

“The operation could not be performed OLD DB provider “SQLNCLI10” for linked server “MYSERVER” was unable to begin a distributed transaction.The transaction manager has disabled its support for remote/network transactions.“To enable inbound and outbound transaction setting on MSDTC, follow the steps given below. Do the same setting on both servers(Local and Remote Server).

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

mstdc

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.

mstdc1

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!

Published at DZone with permission of Prashanth Jayaram, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Comments
X