OPENQUERY and parameters

• dj_meier

  SSCrazy

  Points: 2732

  More actions

  November 21, 2002 at 11:08 am

  #80408

  Is there a way to pass a parameter to the OPENQUERY function? Here is what I'm try to accomplish...I want to call a function w/ parameters on a linked server:

  SELECT SERVER2.db1.dbo.fn_MyFunction(@param1)

  This gives a syntax error, so I tried this:

  SELECT * FROM OPENQUERY(SERVER2, 'SELECT val1=db1.dbo.fn_MyFunction(@param1)')

  This also gives an error.

  What is the best way to call a function on a remote/linked server?

  -Dan

  
  -Dan

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  November 21, 2002 at 12:51 pm

  #442017

  I don't believe functions are supported on linked servers either change it to an SP or use some really bad dynamic SQL (string building).

  I am not 100% so I might be proved wrong

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons

• dj_meier

  SSCrazy

  Points: 2732

  More actions

  November 21, 2002 at 1:14 pm

  #442019

  FYI... just another tidbit of info. It works if I use a hardcoded paramenter:

  SELECT * FROM OPENQUERY(SERVER2, 'SELECT val1=db1.dbo.fn_MyFunction(21)')
  
  

  -Dan

  
  -Dan

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  November 22, 2002 at 2:32 am

  #442066

  Try

  DECLARE @sql nvarchar(4000)

  SET@sql = 'SELECT * FROM OPENQUERY(SERVER2, ''SELECT val1=db1.dbo.fn_MyFunction(' + @param1 + ')'')'

  EXECsp_executesql @sql

  Far away is close at hand in the images of elsewhere.
  Anon.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 22, 2002 at 4:39 am

  #442076

  Unfortunately when they designed OPENQUERY they did not allow for optional parameters to be passed. The only way is to build the query string and execute via EXECUTE or sp_executesql (later being prefered method) as suggested in the previous post by David.