We use EXEC or EXECUTE command almost every day of our database life. Its 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/DBA’s are aware of this feature. Historically, EXEC / EXECUTE executed commands locally, meaning if you have a stored procedure, you can only run that on your local instance of SQL Server.
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 feature, you will need to establish a linked server connection from your local SQL Server instance to the remote SQL Server instance.
Lets take an example of using EXEC or EXECUTE:
Step 1 – Setup a Linked Server
EXEC SP_AddLinkedServer 'SQLHAKANI','SQL Server' Go
Step 2 – Enable Linked Server Connection to allow RPC Calls
Exec SP_ServerOption 'SQLHAKANI','RPC OUT',TRUE Go
Step 3 – Execute the query on a remote server using EXECUTE
Execute ('Select * from Sys.Databases') AT [SQLHAKANI] Go
Here’s another example of using EXECUTE
Execute ('Master.[dbo].[SP_HELPDB] ''Execute''') AT [SQLHAKANI] Go --Alternatively, you can also execute a parameterized stored procedure EXECUTE ('SQLOPS.[dbo].[SP_HelpDB] ''MASTER''') AT [SQLHAKANI] Go