SQL Server Discuss Execute(SQL) AT LinkedServer

Prashanth Jayaram
8y
2.3k
0
1

Blog

Abstract

This blog details the use of "Execute AT Linked Server" 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 trying to manipulate the data and write the complex queries with the heterogeneous data sources.

Introduction

I recently ended up in calling a remote stored procedure(“X”) from a remote machine (“Y”) and the script required to pull the data, which is 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 have been tried to execute SP on remote Server are given below.

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 Linked Server.

Prerequisites

Make sure RPC and RPC Out parameters are set to true.
MSDTC is enabled to run the 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 > ;

SQL can be a single query or a sequence of statements, dynamic SQL or be entirely static. The Linked Server can 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 the distributed queries with heterogeneous data source, the use of EXECUTE … AT LinkedServer works best. 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

It 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. The OPENROWSET command operates the same as the OPENQUERY command but OpenRowSet provides a flexibility in creating the 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 these type of situations within SP itself, i.e. avoiding the use of temp tables.

Error 2

Unable to begin Distributed transaction

“The operation can 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'

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 can 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 the 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).
The example given below shows the execution of the distributed query, which is executed on the remote Server, which fetches the data and writes 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. In many cases, the simple approach may ease out the problem.

The OPENQUERY/OPENROWSET guarantees 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's row set doesn't hold any temporary tables. When SQL is getting complex and needs to query heterogeneous data source, it may get little cumbersome. EXEC() AT is similar to OPENQUERY / OPENQUERY, where 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 blog was helpful to you.

References

Technet