OPENQUERY and parameters

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

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 · Answer 2

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

David Burrows SSC Guru Points: 65142 More actions · Answer 3

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 · Answer 4

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.