none
remote call function by linkserver

    Question

  • Hi Every Body

    I am running remote function  by linkserver ,My problem is that i want to set out put of function in variable as below:

    Declare @DBRemote [nvarchar](100);
    Declare @SqlRemote [nvarchar](500);
    Declare @result INT;
    set @DBRemote = '[TfiAutomationMain]'

    set @SqlRemote = 'SELECT * FROM OPENQUERY([10.0.0.139], ''SELECT ' + @DBRemote +  '.dbo.fn_GetInStock('+ CONVERT(NVARCHAR(50),1395) +')'')'

    SELECT @result
    EXEC @result = @SqlRemote --<= instead of EXEC  @SqlRemote

    SELECT @result

    but it makes error.

    Thanks for any guides.

    Regards

    Ali

    Tuesday, April 08, 2014 12:37 PM

Answers

  • Try

    Declare @DBRemote [nvarchar](100);
    Declare @SqlRemote [nvarchar](500);
    Declare @Output table (Result INT);
    set @DBRemote = '[TfiAutomationMain]'
    
    declare @Result int
    
    set @SqlRemote = 'SELECT * FROM OPENQUERY([10.0.0.139], ''SELECT ' + @DBRemote +  '.dbo.fn_GetInStock('+ CONVERT(NVARCHAR(50),1395) +')'')'
    --print @SQLRemote
    
    insert into @Output (Result)
    execute (@SQLRemote);
    
    select @Result = result from @Output
    
    select @Result as myResult -- remote execution result of the scalar function
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 08, 2014 6:02 PM
    Moderator

All replies

  • Try

    Declare @DBRemote [nvarchar](100);
    Declare @SqlRemote [nvarchar](500);
    Declare @Output table (Result INT);
    set @DBRemote = '[TfiAutomationMain]'
    
    declare @Result int
    
    set @SqlRemote = 'SELECT * FROM OPENQUERY([10.0.0.139], ''SELECT ' + @DBRemote +  '.dbo.fn_GetInStock('+ CONVERT(NVARCHAR(50),1395) +')'')'
    --print @SQLRemote
    
    insert into @Output (Result)
    execute (@SQLRemote);
    
    select @Result = result from @Output
    
    select @Result as myResult -- remote execution result of the scalar function
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 08, 2014 6:02 PM
    Moderator
  • Thanks for your guides

    Regards

    Ali

    Wednesday, April 09, 2014 7:39 AM