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>;