Tuesday, April 25, 2017
 
 
     

SQL Server - Execute Commands Remotely Using AT Clause

By Saleem Hakani on 9/21/2014

We use EXEC or EXECUTE command almost every day of our database life. It’s a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.)  and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers are aware of this feature. Historically, EXEC/EXECUTE executed commands locally, meaning if you have a SPROC you can only run that on your local instance of SQL Server. That’s changed now.

EXEC or EXECUTE statement now supports a parameter called AT which allows you to specify the SQL Server instance at which you plan to execute your command from your local SQL Server instance. To use this powerful feature, you will need to establish a linked server from your local SQL Server instance to the remote SQL Server instance. This is required for establishing connections between both local and remote SQL Servers.

Let’s take an example of using EXEC or EXECUTE:

STEP1: Setup a Linked Server

EXEC SP_AddLinkedServer 'SQLHAKANI', 'SQL Server'

 

STEP2: Enable Linked Server Connection to Allow RPC Calls (RPC = Remote Procedure Call)

EXEC SP_ServerOption 'SQLHAKANI','RPC OUT',TRUE

 

STEP3: Execute Your Query

EXECUTE ('Select * from Sys.Databases') AT [SQLHAKANI]

 

STEP4: Another Example: Execute a Stored Procedure

EXECUTE ('Master.[dbo].[SP_HELPDB] ''Execute''') AT [SQLHAKANI]

Alternatively, you can also execute a parameterized stored procedure

EXECUTE ('SQLOPS.[dbo].[SP_HelpDB] ''MASTER''') AT [SQLHAKANI]

AT Clause
EXECUTE
Linked Server
Remotely Execute Commands
Minimize
SQL Article Tags
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM