SQL SERVER TIPS n°4 : Remote Execution using EXECUTE command

  • Article

In previous versions of SQL Server (Prior to SQL Server 2005) you could execute EXECUTE command only on the local server.

With SQL Server 2005 EXECUTE command now has AT parameter which can be used for executing the statement on a remote linked server.

Example:

Let's setup a linked serverusing SP_AddLinkedServer and point it to some other SQL Server:

--Add the linked server to the local machine

EXEC sp_addlinkedserver 'SQLSERVER2', 'SQL Server'

--Enable the linked server to allow RPC calls

Exec SP_Serveroption 'SQLSERVER2','RPC OUT',TRUE

Now you are ready to execute T-SQL statements across linked servers using AT command:

EXEC('Select * from AdventureWorksDW..DatabaseLog') AT SQLSERVER2