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

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

Expand Messages
  • Noman Aftab
    Aug 4, 2010
      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


      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

      First Attempt - To my knowledge passing variable strings to the OpenQuery
      function is not supported. You might take a look at

      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.


      --- 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
      > --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
    • Show all 13 messages in this topic