Dynamic SQL execution on remote SQL Server using EXEC AT
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions.
In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.
SQL Server 2005 introduces an enhancement to the EXEC command to allow dynamic SQL execution on the linked server. The new EXEC AT command addresses the above limitations of OPENQUERY and OPENROWSET. EXEC AT specifies that command_string is executed against linked_server_name and results, if any, are returned to the client. The linked_server_name must refer to an existing linked server definition in the local server.
See the whole article with examples on Dynamic SQL execution on remote SQL Server using EXEC AT