SQL Server: Discuss Execute(SQL) At LinkedServer

Abstract

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

Introduction

I recently end up in calling a remote stored procedure(“X”) from a remote machine (“Y”)and the script needed to pull data store it 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 have tried to execute the SP on remote server are

Calling with four part naming convention
Using OpenQuery and OpenRowSet
Execute At LinkedServer

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

↑ Return to Top

Pre-requisites

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

↑ Return to Top

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 the simple SELECT statement. By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;’ to SQL string 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.

SELECT* FROMOPENQUERY(ADDBSP18,'SET FMTONLY ON; exec MES_DW2_PROD.dbo.SPTrans_Tracs_Pull_Prashanth ''7/1/2016'',''7/31/2016''')

The reason for the error is that when you execute a stored procedure on a linked server, the provider first tries to determine the shape of the resulting row set. It does this by issuing SET FMTONLY ON; There are multiple ways to handle this type of situations within the SP itself that is by avoiding the use temp tables.

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 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 distributed query executed on the remote server which fetches the data and writes it an another remote SQL instance.

mstdc1

↑ Return to Top

Conclusion

You can safely use Linked Servers in a production setting but do your research and test before settling on a solution. In many cases, the simple approach may ease out the problem.

The 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 for simple SQL statements and it row set doesn’t hold in any temporary tables. When the SQL is getting complex and needs to query heterogeneous data source, 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!

↑ Return to Top