none
Returning Data from a Linked Server EXEC

    Question

  • I am trying to call a EXEC and pass a Sales Order # to it, returning the # of Tickets:

    USE [ATPROD]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_XX_TESTFN] 
    @SalesOrderNo nvarchar(7), @NumNow int OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @xNumNow int 
    EXEC ('SELECT count(*) FROM JT_Transaction WHERE SalesOrderNo = ''' +@SalesOrderNo+ ''' ', @NumNow OUTPUT) AT MAS90RW;
    		
    END

    According to Microsoft, the variable @NumNow should have been passed to the calling EXEC:

    USE [ATPROD]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SP_XX_TESTExec] 
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @NumNow int, @SalesOrderNo nvarchar(7), @xNumNow int, @Result int 
    SET @SalesOrderNo = '0008410'
    set @xNumNow = null 
    set @NumNow = null 
    exec @Result = dbo.sp_XX_TESTFN @SalesOrderNo,  @NumNow = @xNumNow OUTPUT 
    SELECT @Result as Result, @xNumNow as NumNow
    		
    END

    The resultant @xNumNow is NULL

    The SP_XX_TESTFN does return 2000+ records but refuses to return that to the calling procedure.

    Anyone know what is going wrong?

     

    Thursday, January 30, 2014 9:21 PM

All replies

  • EXEC ('SELECT count(*) FROM JT_Transaction WHERE SalesOrderNo = ''' +@SalesOrderNo+ ''' ', @NumNow OUTPUT) AT MAS90RW;

    This is a new construct for me. Could you provide the url where Microsoft gave you this suggestion?

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, January 30, 2014 9:40 PM
  • Hi Mako_M,

    Based on your description, the issue is T-SQL related. I have moved this thread to the T-SQL forum so that you can get more professional support.

    If I have anything misunderstood, please feel free to let me know.

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, February 03, 2014 8:10 AM
    Moderator
  • You need to use a parameter marker:

    EXEC ('SELECT ? = count(*) FROM JT_Transaction WHERE SalesOrderNo = ?',
          @NumNow OUTPUT, @SalesOrderNo) AT MAS90RW;

    Note that I also use a parameter marker for the input parameter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 03, 2014 8:29 AM
  • Mako,

    You can go with this EXEC statement witht he parameter marks as suggested by Erland.

    Alternatively, another method would be is to use sp_executesql to supply the input parameters and output parameters. You can have the linked server name and database_name as a part of the table_name in the SQL (dynamic) query itself - as a fully qualified four part name.

    Something like below:

    /*
    Query to obtain the name of the object in tempdb database
    given the object_id as input
    -Achieved Dynamically via sp_executesql
    */
    declare @input int
    declare @output varchar(255)
    declare @sql nvarchar(max)
    
    set @input = 27
    
    --mentioning input,output parameters and linked_server name,db_name as a part of the query itself
    set @sql='SELECT TOP 1 @name=name from l_servername.tempdb.sys.objects where object_id=@id'
    
    --1st parametr	:	the query
    --2nd parameter :	definition for paramters used in query
    --3rd parameter	:	assignment of real time variables to the query variables
    --all paramter string to be of NVARCHAR type
    exec sp_executesql @sql,N'@name varchar(255) OUTPUT,@id int',@name=@output OUTPUT,@id=@input
    
    select @output as result_output
    

    For further info on sp_executesql:

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    https://support.microsoft.com/kb/262499


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Monday, February 03, 2014 9:07 AM
  • Mako,

    You can go with this EXEC statement witht he parameter marks as suggested by Erland. Alternativel, another method would be is to use sp_executesql to supply the input parameters and output parameters. You can have the database_name as a part of the table_name in the SQL (dynamic) query itself - as a fully qualified three part name.

    Something like below:

    /*
    Query to obtain the name of the object in tempdb database
    given the object_id as input
    -Achieved Dynamically via sp_executesql
    */
    declare @input int
    declare @output varchar(255)
    declare @sql nvarchar(max)
    
    set @input = 27
    
    --mentioning input,output parameters and db_name as a part of the query itself
    set @sql='SELECT TOP 1 @name=name from tempdb.sys.objects where object_id=@id'
    
    --1st parametr	:	the query
    --2nd parameter :	definition for paramters used in query
    --3rd parameter	:	assignment of real time variables to the query variables
    --all paramter string to be of NVARCHAR type
    exec sp_executesql @sql,N'@name varchar(255) OUTPUT,@id int',@name=@output OUTPUT,@id=@input
    
    select @output as result_output
    

    For further info on sp_executesql:

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    https://support.microsoft.com/kb/262499


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Sorry not clear from this suggestion how you'll execute this at linked server

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, February 03, 2014 9:10 AM
  • Please refer the below link:

    http://sql-sudhir.blogspot.in/2009/10/how-to-use-exec-at-linked-server.html

    Monday, February 03, 2014 9:13 AM
  • Visakh,

    I had noticed the absence of linked server name in my query imemdiately after posting the result and edited the post soon after. I gues thiss code should work in the OP's case

    Let me know in case of any discrpancies or if I am wrong in any case

    Thanks.


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Monday, February 03, 2014 9:16 AM
  • Alternatively, another method would be is to use sp_executesql to supply the input parameters and output parameters.

    Well, you can do:

    SELECT @sql = SELECT @NumNow = count(*) FROM JT_Transaction WHERE SalesOrderNo = @SalesOrderId'
    EXEC MyServer.MyDatabase.sys.sp_executesql @sql,
         '@NumNow int OUTPUT, @SalesOrderNo int',
         @NumNow OUTPUT, @SalesOrderNo

    But only if the remote server is another SQL Server instance. There is no sp_executesql on Oracle or Access.

    And, it is possible to do as you suggested, but not that this can be quite different for a more complext query. If you are really unfortunate, the query includes something which prevents remoting, so that all table data has to be dragged over to the local server to perform join. If you use EXEC() AT, or sp_executesql as showed above, the entire query is executed on the remote server and all that travels back is the data.

    Besides, the poster asked how to used EXEC() AT.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 03, 2014 11:53 AM