Dynamic SQL execution on remote SQL Server using EXEC AT

By:   |   Comments (17)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dynamic SQL


Problem

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.

Solution

OPENQUERY table function executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. Although the query may return multiple result sets, OPENQUERY returns only the first one.

The problems with OPENQUERY are; first, OPENQUERY does not accept variables for its arguments, in other words it must be static (although here is a trick to overcome it), second, only one result-set is returned and third, OPENQUERY is used in the FROM clause which is quite limiting in terms when you have to invoke an executable statement, such as a DDL statement against the target server. Similar limitation apply to the OPENROWSET table function as well.

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.

Example

So now let's see how we can use it. In the below given table,

  • Script 1 creates a linked server definition for FARAWAYSERVER server.
  • Script 2 executes a simple SELECT statement on the linked server which returns a single result-set.
  • Script 3 executes two SELECT statements on the linked server and hence as result of this two result-sets are returned to the client.
  • Script 4 executes a SELECT statement on the linked server and passes two arguments dynamically at run-time.
  • Script 5 uses script 4, but this time it uses variables to pass argument values dynamically at run-time.
  • Script 6 executes the executable code on the linked server, in this case its creating a table in tempdb on the linked server. And finally
  • Script 7 drops all the objects created in this session.
--Script : EXEC AT Command

--Script 1 : Create a linked server 
EXEC sp_addlinkedserver 'FARAWAYSERVER', 'SQL Server'

--Script 2 : Execute a simple SELECT statement on the linked server
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer') AT [FARAWAYSERVER];
GO

--Script 3 : Executing multiple SELECT statements on linked server and getting multiple resultsets
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [FARAWAYSERVER];
GO

--Script 4 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [FARAWAYSERVER];
GO

--Script 5 : Execute a SELECT statement on linked serer and pass two arguments at dynamically
--by using variables
DECLARE @CustomerID AS INT
DECLARE @LastName AS VARCHAR(100)
SET @CustomerID = 10
SET @LastName = 'Garza'
EXEC ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [FARAWAYSERVER];
GO

--Script 6 : Execute a DDL statement on linked server
EXEC (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1
(
Column1 INT
)' ) AT [FARAWAYSERVER];

--Script 7 : Once you are done with your testing, clean up created objects
EXEC (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1'
) AT [FARAWAYSERVER];
EXEC sp_dropserver 'FARAWAYSERVER'

Notes

  • You may get error "Server 'myserver' is not configured for RPC." for that you will not need to enable RPC with the commands given below:
exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'
  • Before you call EXECUTE with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection.
  • If you have a named instance you will probably need to use sp_addlnkedserver as follows to create the linked server name without a slash.  So the linked server is referenced as "SQL2005", but this will connect to instance "SERVER1\SQL2005".
EXEC sp_addlinkedserver @server='SQL2005', @srvproduct='', @provider='SQLNCLI', @datasrc='SERVER1\SQL2005'
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, November 21, 2022 - 2:23:04 PM - Graham Back To Top (90703)
Here is an example of how to dynamically set the server to which you are running against:

DECLARE @Results TABLE (result SYSNAME)

DECLARE @RemoteSQL NVARCHAR(MAX) = 'SELECT @@ServerName'
, @LinkedServer SYSNAME = 'Linked_Server'

DECLARE @DynamicExecuteAt NVARCHAR(MAX) = CONCAT(N'EXECUTE (@RemoteSQL) AT ',QUOTENAME(@LinkedServer))

INSERT INTO @Results
EXECUTE sys.sp_executesql @DynamicExecuteAt, N'@RemoteSQL AS NVARCHAR(MAX)', @RemoteSQL

SELECT * FROM @Results

Wednesday, October 28, 2020 - 4:54:02 PM - Sirish Back To Top (86710)
Hello,

--SQL Server to Teradata open query
--The stored procedure returns the resultset but i need to capture it in a table. Can i do that without creating a physical/variable table?
I am trying to see if I can mimmic a SELECT INTO # ret_tbl with the structure and may be data

How do i capture the result from the below query without creating a table in advance
EXECUTE (@test_query ) AT [Linkedserver]

Wednesday, February 12, 2020 - 3:53:34 AM - rawk Back To Top (84369)

Nice article!

What can I go about parameterizing AT [FARAWAYSERVER]? E.g. DECLARE @servername nvarchar(20) and I execute:

SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT @servername;

I get error "Incorrect syntax near '@servername'." I also tried to wrap in this way: "[{servername}]" but no luck.

Thanks for your time in advance. Much appreciated.


Friday, September 13, 2019 - 12:45:22 PM - Peter Back To Top (82412)

Hi Arshad,

I would like the result of the linked server query (EXEC AT) to write to a variable.

Do you have an example of a query that will do this?

Thanks, Peter 


Friday, December 14, 2018 - 4:56:06 PM - Jose Medina Back To Top (78498)

HI Arshad

thanks for sharing your knowlage. i can execute the RPC perfectly from my SSMS, but when my program in c# runs the call using Entityframework (6) i am getting error "the operation could not be performed because ole db provider sqlncli11 for linked server is unable to begin a distributed transaction 

any help will be appreciated 

Jose


Tuesday, April 7, 2015 - 4:25:37 PM - Ashish Back To Top (36848)

Hello Arshad,

Thank You for the article. I am not able to insert the output from the EXEC() AT [linserver name ] to temp table. I need to insert the out put of the exec query to temp teble to do other operation . Could you please suggest me. 

EXEC (' select a.pid,b.desc

          from table1 a

           join table2 b a.pid=b.pid  ') AT [linkserver name]

I need to insert the out of this to temptable. Thanks


Friday, February 28, 2014 - 5:46:47 PM - Lance Back To Top (29618)

Did anyone come up with a solution for Nirav Gajjars' question?  I am running into the same issue.  Creating the linked server and executing the proc is simple enough, the issue for me is exactly the same as Niravs', that I want to create a variable  (@Servername for example) and pass that as the 'ServerName' after the AT command.  When I attempt to do this, I receive an error that it requires an ID or a quoted_id.  When I do that, it literally uses the variable name as the 'Servername'.  Any ideas?

 

Thanks in advance


Friday, January 24, 2014 - 8:34:17 AM - Chalapathi Pagidakula Back To Top (28215)

Thanks a lot its helpful for all and Excellent topic


Wednesday, January 8, 2014 - 10:37:28 PM - Samo Back To Top (27997)

Good one..


Monday, July 1, 2013 - 5:31:50 AM - Obaid Altaf Back To Top (25642)

How to call an Oracle Function with multi out Parameters though LINKSERVER

 

i need help as i am facing this problem. when i pass 2 output parameter to a function through a linked server I am getting this error:

for 1 out parameter its working fine but when i use two output parameters it gives this error:

 

“Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

 

Here is the query:

 

DECLARE @RET INT

 

EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT

, @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH

, @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE

, @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )

AT linkserver_name;

waiting for the reply.


Thursday, January 10, 2013 - 11:27:35 AM - enzo Back To Top (21375)

hi, excellent post but.... can i capture result in a temp variable from a execute at linked server (access table)?

thanks in advance


Wednesday, October 24, 2012 - 10:23:59 AM - H.P. Debets Back To Top (20070)

Thank you so much! Almost 3 years after your post, but this does solve another issue with a BIG (or should i say small ;) ) limitation with openquery: the latter only accepts 8000 chars for the query argument, the EXECUTE () AT <linkedservername> does not have that limitation. I just passed a 13K+ query to an Oracle server from SQL 2005 and it works....


Saturday, May 5, 2012 - 3:28:38 AM - Nirav Gajjar Back To Top (17279)

 

HI Arshad,

 

Its really very nice blog.

 

i have one more quetion regarding linked server.

 

can you please give suggetion for that.

 

declare

 

 

@nameasvarchar(300

 

)

 

 

 

select

 

 

@name=namefromLinked_Server.master.sys.servers

i need to to get value in variable using EXEX AT.


Tuesday, March 20, 2012 - 8:52:22 AM - Kevin Back To Top (16530)

Your article has text "although here is a trick to overcome it" which looks like it should contain a link to a trick, but there is none.


Thursday, August 20, 2009 - 4:50:09 AM - mcpjwebster Back To Top (3927)

Can this function be used in a sp?  i am trying to add it to a sp and want to pass the server name after the AT code but it is not working, any suggestions.


Thursday, May 28, 2009 - 1:11:33 PM - abeljda Back To Top (3468)

Great tip Arshad. Was wondering if you have had much experience, good or bad using linked servers? We stay away from them here simply due to the performance issues surrounding them. Would appreciate any feedback you ahve. Thanks.!


Monday, May 25, 2009 - 11:55:47 PM - ihar_ku Back To Top (3448)

Excellent topic!!! Thanks a lot!!!















get free sql tips
agree to terms