Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Dynamic SQL execution on remote SQL Server using EXEC AT


By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 07, 2015 - 4:25:37 PM - Ashish Back To Top

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

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

Thanks a lot its helpful for all and Excellent topic


Wednesday, January 08, 2014 - 10:37:28 PM - Samo Back To Top

Good one..


Monday, July 01, 2013 - 5:31:50 AM - Obaid Altaf Back To Top

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

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

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 05, 2012 - 3:28:38 AM - Nirav Gajjar Back To Top

 

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

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

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

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

Excellent topic!!! Thanks a lot!!!


Learn more about SQL Server tools