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

4552Re: [SQLQueriesNoCode] Re: Calling UDF function on linked server with parameters

Expand Messages
  • Noman Aftab
    Aug 8, 2010
      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]
    • Show all 13 messages in this topic