Loading ...
Sorry, an error occurred while loading the content.

Calling UDF function on linked server with parameters

Expand Messages
  • Noman Aftab
    Hi, I am trying to call a function on a linked server with the following sql: --declarations declare @prefix varchar(1) declare @Code varchar(10) declare
    Message 1 of 13 , Aug 4, 2010
    • 0 Attachment
      Hi,
      I am trying to call a function on a linked server with the following sql:


      --declarations
      declare @prefix varchar(1)
      declare @Code varchar(10)
      declare @number varchar(10)
      declare @cost decimal(22,8)
      declare @str1 varchar(max)

      --values
      set @prefix = 'V'
      set @Code = 'A100'
      set @number = 'P90'


      --first attempt (concatenating a string and passing it to openquery)
      set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
      '''+@number+''') as COST'
      SELECT @cost = COST from OpenQuery(SERVER1, @str1)
      --error on first attempt (Incorrect syntax near '@str1'.)

      --second attempt (directly running the function)
      select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
      --SERVER1 is the name of the linked server
      --error on second attempt (Invalid column name 'SERVER1'.)

      --third attempt (concatenation without variable in openquery)
      SELECT @cost = COST from OPENQUERY(SERVER1, 'select
      DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as COST')
      --error on third attempt (Incorrect syntax near '+'.)

      --fourth attempt (which works, if values are hard coded in the string)
      SELECT @cost = COST from OPENQUERY(SERVER1, 'select
      DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')


      Please Help!



      Best Wishes,
      Noman Aftab
    • p_livengood
      Noman; First Attempt - To my knowledge passing variable strings to the OpenQuery function is not supported. You might take a look at
      Message 2 of 13 , Aug 4, 2010
      • 0 Attachment
        Noman;
        First Attempt - To my knowledge passing variable strings to the OpenQuery function is not supported. You might take a look at http://support.microsoft.com/kb/314520.

        Second Attempt - UDFs aren't supported directly on linked servers. Please look at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.

        Third Attempt - Quotes get tricky, but the article from First Attempt has a link to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1, 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''', '''''+@number+''''') as COST')

        Forth Attemp - This shows that it will work, its just a matter of getting the quotes and stuff correct.

        HTH
        Paul


        --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
        >
        >
        >
        >
        > Hi,
        > I am trying to call a function on a linked server with the following sql:
        >
        >
        > --declarations
        > declare @prefix varchar(1)
        > declare @Code varchar(10)
        > declare @number varchar(10)
        > declare @cost decimal(22,8)
        > declare @str1 varchar(max)
        >
        > --values
        > set @prefix = 'V'
        > set @Code = 'A100'
        > set @number = 'P90'
        >
        >
        > --first attempt (concatenating a string and passing it to openquery)
        > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
        > '''+@number+''') as COST'
        > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
        > --error on first attempt (Incorrect syntax near '@str1'.)
        >
        > --second attempt (directly running the function)
        > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
        > --SERVER1 is the name of the linked server
        > --error on second attempt (Invalid column name 'SERVER1'.)
        >
        > --third attempt (concatenation without variable in openquery)
        > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
        > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as COST')
        > --error on third attempt (Incorrect syntax near '+'.)
        >
        > --fourth attempt (which works, if values are hard coded in the string)
        > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
        > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
        >
        >
        > Please Help!
        >
        >
        >
        > Best Wishes,
        > Noman Aftab
        >
      • Noman Aftab
        exec SERVER1.DB1.dbo.sp_executesql N SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number) , N @code varchar(50), @number varchar(50) , @code= @temp, @number =
        Message 3 of 13 , Aug 4, 2010
        • 0 Attachment
          exec SERVER1.DB1.dbo.sp_executesql
          N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
          N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num

          Thanks Paul for the link. The above sql has worked, but how to get the output
          into a scalar variable?

          i.e. output into @cost for further use.
          Best Wishes,
          Noman Aftab


          http://www.jalandhari.qsh.eu





          ________________________________
          From: p_livengood <p_livengood@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Wed, 4 August, 2010 10:30:34 PM
          Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
          parameters




          Noman;
          First Attempt - To my knowledge passing variable strings to the OpenQuery
          function is not supported. You might take a look at
          http://support.microsoft.com/kb/314520.

          Second Attempt - UDFs aren't supported directly on linked servers. Please look
          at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.

          Third Attempt - Quotes get tricky, but the article from First Attempt has a link

          to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
          'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
          '''''+@number+''''') as COST')

          Forth Attemp - This shows that it will work, its just a matter of getting the
          quotes and stuff correct.


          HTH
          Paul

          --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
          >
          >
          >
          >
          > Hi,
          > I am trying to call a function on a linked server with the following sql:
          >
          >
          > --declarations
          > declare @prefix varchar(1)
          > declare @Code varchar(10)
          > declare @number varchar(10)
          > declare @cost decimal(22,8)
          > declare @str1 varchar(max)
          >
          > --values
          > set @prefix = 'V'
          > set @Code = 'A100'
          > set @number = 'P90'
          >
          >
          > --first attempt (concatenating a string and passing it to openquery)
          > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
          > '''+@number+''') as COST'
          > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
          > --error on first attempt (Incorrect syntax near '@str1'.)
          >
          > --second attempt (directly running the function)
          > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
          > --SERVER1 is the name of the linked server
          > --error on second attempt (Invalid column name 'SERVER1'.)
          >
          > --third attempt (concatenation without variable in openquery)
          > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
          > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
          >COST')
          > --error on third attempt (Incorrect syntax near '+'.)
          >
          > --fourth attempt (which works, if values are hard coded in the string)
          > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
          > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
          >
          >
          > Please Help!
          >
          >
          >
          > Best Wishes,
          > Noman Aftab
          >
        • p_livengood
          To get the results assigned to your local variable @cost you will need to use an output variable in the EXECUTE SQL statement. I believe the following should
          Message 4 of 13 , Aug 5, 2010
          • 0 Attachment
            To get the results assigned to your local variable @cost you will need to use an output variable in the EXECUTE SQL statement. I believe the following should work

            exec SERVER1.DB1.dbo.sp_executesql
            N'SELECT @CostOUT = DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
            N'@code varchar(50), @number varchar(50), @CostOUT NUMERIC(18,2) OUTPUT',
            @code = @temp,
            @number = @num,
            @costOUT = @cost OUTPUT


            HTH
            Paul
            --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
            >
            > exec SERVER1.DB1.dbo.sp_executesql
            > N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
            > N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num
            >
            > Thanks Paul for the link. The above sql has worked, but how to get the output
            > into a scalar variable?
            >
            > i.e. output into @cost for further use.
            > Best Wishes,
            > Noman Aftab
            >
            >
            > http://www.jalandhari.qsh.eu
            >
            >
            >
            >
            >
            > ________________________________
            > From: p_livengood <p_livengood@...>
            > To: SQLQueriesNoCode@yahoogroups.com
            > Sent: Wed, 4 August, 2010 10:30:34 PM
            > Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
            > parameters
            >
            >
            >
            >
            > Noman;
            > First Attempt - To my knowledge passing variable strings to the OpenQuery
            > function is not supported. You might take a look at
            > http://support.microsoft.com/kb/314520.
            >
            > Second Attempt - UDFs aren't supported directly on linked servers. Please look
            > at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.
            >
            > Third Attempt - Quotes get tricky, but the article from First Attempt has a link
            >
            > to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
            > 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
            > '''''+@number+''''') as COST')
            >
            > Forth Attemp - This shows that it will work, its just a matter of getting the
            > quotes and stuff correct.
            >
            >
            > HTH
            > Paul
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@> wrote:
            > >
            > >
            > >
            > >
            > > Hi,
            > > I am trying to call a function on a linked server with the following sql:
            > >
            > >
            > > --declarations
            > > declare @prefix varchar(1)
            > > declare @Code varchar(10)
            > > declare @number varchar(10)
            > > declare @cost decimal(22,8)
            > > declare @str1 varchar(max)
            > >
            > > --values
            > > set @prefix = 'V'
            > > set @Code = 'A100'
            > > set @number = 'P90'
            > >
            > >
            > > --first attempt (concatenating a string and passing it to openquery)
            > > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
            > > '''+@number+''') as COST'
            > > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
            > > --error on first attempt (Incorrect syntax near '@str1'.)
            > >
            > > --second attempt (directly running the function)
            > > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
            > > --SERVER1 is the name of the linked server
            > > --error on second attempt (Invalid column name 'SERVER1'.)
            > >
            > > --third attempt (concatenation without variable in openquery)
            > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
            > > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
            > >COST')
            > > --error on third attempt (Incorrect syntax near '+'.)
            > >
            > > --fourth attempt (which works, if values are hard coded in the string)
            > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
            > > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
            > >
            > >
            > > Please Help!
            > >
            > >
            > >
            > > Best Wishes,
            > > Noman Aftab
            > >
            >
          • Noman Aftab
            Thanks again Paul, the OUTPUT parameter worked. (Y) Best Wishes, Noman Aftab http://www.corpus.quran.com/wordbyword.jsp http://www.jalandhari.qsh.eu
            Message 5 of 13 , Aug 8, 2010
            • 0 Attachment
              Thanks again Paul, the OUTPUT parameter worked. (Y)
              Best Wishes,
              Noman Aftab


              http://www.corpus.quran.com/wordbyword.jsp
              http://www.jalandhari.qsh.eu





              ________________________________
              From: p_livengood <p_livengood@...>
              To: SQLQueriesNoCode@yahoogroups.com
              Sent: Fri, 6 August, 2010 12:17:43 AM
              Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
              parameters


              To get the results assigned to your local variable @cost you will need to use an
              output variable in the EXECUTE SQL statement. I believe the following should
              work

              exec SERVER1.DB1.dbo.sp_executesql
              N'SELECT @CostOUT = DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
              N'@code varchar(50), @number varchar(50), @CostOUT NUMERIC(18,2) OUTPUT',
              @code = @temp,
              @number = @num,
              @costOUT = @cost OUTPUT

              HTH
              Paul
              --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
              >
              > exec SERVER1.DB1.dbo.sp_executesql
              > N'SELECT DB1.dbo.WML_fn_get_BD_POCost(@code, @number)',
              > N'@code varchar(50), @number varchar(50)', @code= @temp, @number = @num
              >
              > Thanks Paul for the link. The above sql has worked, but how to get the output
              > into a scalar variable?
              >
              > i.e. output into @cost for further use.
              > Best Wishes,
              > Noman Aftab
              >
              >
              > http://www.jalandhari.qsh.eu
              >
              >
              >
              >
              >
              > ________________________________
              > From: p_livengood <p_livengood@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Sent: Wed, 4 August, 2010 10:30:34 PM
              > Subject: [SQLQueriesNoCode] Re: Calling UDF function on linked server with
              > parameters
              >
              >
              >
              >
              > Noman;
              > First Attempt - To my knowledge passing variable strings to the OpenQuery
              > function is not supported. You might take a look at
              > http://support.microsoft.com/kb/314520.
              >
              > Second Attempt - UDFs aren't supported directly on linked servers. Please look
              >
              > at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138.
              >
              > Third Attempt - Quotes get tricky, but the article from First Attempt has a
              >link
              >
              >
              > to article on how to do this....try SELECT @cost = COST from OPENQUERY(SERVER1,
              >
              > 'select DB1.dbo.WML_fn_get_BD_POCost('''''+@prefix + @Code+''''',
              > '''''+@number+''''') as COST')
              >
              > Forth Attemp - This shows that it will work, its just a matter of getting the
              > quotes and stuff correct.
              >
              >
              > HTH
              > Paul
              >
              > --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@> wrote:
              > >
              > >
              > >
              > >
              > > Hi,
              > > I am trying to call a function on a linked server with the following sql:
              > >
              > >
              > > --declarations
              > > declare @prefix varchar(1)
              > > declare @Code varchar(10)
              > > declare @number varchar(10)
              > > declare @cost decimal(22,8)
              > > declare @str1 varchar(max)
              > >
              > > --values
              > > set @prefix = 'V'
              > > set @Code = 'A100'
              > > set @number = 'P90'
              > >
              > >
              > > --first attempt (concatenating a string and passing it to openquery)
              > > set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
              > > '''+@number+''') as COST'
              > > SELECT @cost = COST from OpenQuery(SERVER1, @str1)
              > > --error on first attempt (Incorrect syntax near '@str1'.)
              > >
              > > --second attempt (directly running the function)
              > > select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as
              COST
              > > --SERVER1 is the name of the linked server
              > > --error on second attempt (Invalid column name 'SERVER1'.)
              > >
              > > --third attempt (concatenation without variable in openquery)
              > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
              > > DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as
              > >COST')
              > > --error on third attempt (Incorrect syntax near '+'.)
              > >
              > > --fourth attempt (which works, if values are hard coded in the string)
              > > SELECT @cost = COST from OPENQUERY(SERVER1, 'select
              > > DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')
              > >
              > >
              > > Please Help!
              > >
              > >
              > >
              > > Best Wishes,
              > > Noman Aftab
              > >
              >






              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.