OPENQUERY

• Minnesota - Viking

  Hall of Fame

  Points: 3469

  More actions

  October 13, 2010 at 10:10 am

  #229636

  I have a bunch of UDFs in Oracle10g. I wanna use them from SQL Server.

  Since i have my Oracle linked to SQL Server.

  I write the query as

  SELECT *

  FROM OPENQUERY(OracleSvr, 'SELECT name, MY_USER_DEFINEd_FUNCTION('234567') FROM joe.titles')

  Where 234567 is my account id which is i/p paramaeter for my UDF.

  Cool. .. It works fine and i get the result.

  BUT WHEN I TRY TO USE A VARIABLE IN PLACE OF actual accoutn number.

  since my column name is 'acctid'

  When i substitute '234567' with '@acctid'

  🙁 I get an error 🙁

  The error simple says that this open query cannot be run blah blah

  Please help

  [font="Verdana"]
  Today is the tomorrow you worried about yesterday:-)[/font]

• Stejones

  SSC Veteran

  Points: 260

  More actions

  October 13, 2010 at 3:45 pm

  #1236537

  if I understand what you are asking, I don't think openquery can use variables. In the past I have had to build out the statements dynamically.

  DECLARE @QryString as nVarchar(MAX)

  SET @QryString = 'SELECT * FROM OPENQUERY(OracleSvr, ''SELECT name, MY_USER_DEFINEd_FUNCTION(''' + @acctid + ''') FROM joe.titles'')'

  EXEC sp_executesql @QryString

  I may have screwed up a quote or two.. sorry if the formatting is off, first post...

• Ed B

  Hall of Fame

  Points: 3666

  More actions

  October 13, 2010 at 4:01 pm

  #1236554

  I've had to do it the same way the previous poster described.

  You might need to convert(nvarchar,@acctid).

  You can also make it work using the @params option for sp_executesql.