passing parameter based queries to remote linked servers...

[kevinbk]

How do you pass parameter based queries to remote linked servers from SQL*Server?

It seems like such a basic functionality that it must be within SQL*Server to call remote non-SQL*Server data sources with a parameter.

OPENQUERY doesn't accept parameters within the query string portion, so the only option I can think of to pass parameters is to build up a query string with the relevant parameter values put in it. I cannot code this ahead of time as the values are not known until runtime.

For example, a userid from a website is passed to a stored procedure, the stored proc calls the remote server with OPENQUERY and some query...

select * from openquery(Linkedserver,'select * from
tableA
where id = 2345')

would be a query that SQL*Server would be able to run against a remote server. (in this case a non SQL*Server linked server)

If I need to supply the id value without hardcoding it in the stored proc I am unable to put in a parameter reference like ...

where id = @param

OpenQuery does not accept parameters. Yet OPENQUERY also doesn't seem to accept a string variable as the second part of the OPENQUERY call.

So in essence SQL*Server lets you call remote boxes if you always know at coding time what the remote condition values will be.
I can't imagine SQL*Server is that dumb. Or am I wrong? SQL*Server is just that dumb.

 

[SQLSister]

After a linked server is defined, a four-part name in the form linked_server_name.catalog.schema.object_name can be used in Transact-SQL statements to reference data objects in that linked server.

So once you have a linked server set up you can reference it in a stored procedure or SQL query using the four part name rather than using the OpenQuery statement.

Questions about posting. See faq183-874

 

[kevinbk]

The question is not how to reference a linked server or any tables it may contain. The question is how to pass query parameter values.

And I will note that not all linkable remote servers and their databases support 4 part naming conventions, and yet they are still queryable. It is on the Microsoft side that the methods like OPENQUERY limit what can be sent to the remote server.

I can build up a string variable that contains the relevant "select" statement components, like "From" and "Where" and even put parameter values into that string.
But, OPENQUERY does not seem to allow anything but a hardcoded single quoted string to be passed to the remote server. How do I work around that?

 

[saustin]

Hi kevinbk,
Did you come up with a solution or work-around to passing a parameter to an openquery ? Need to do that or call an oracle stored procedure from a sql stored procedure and pass it parameters. Thanks, Steve.

 

[kevinbk]

Yes, I found a reference to heavily single quoted methods. I forget where. But, the following query is based on that approach and I was able to get it to work and with a little tweaking I greatly reduced the number of quotes in the query...

declare @someSQL VARCHAR(200)
declare @somePATID VARCHAR(100)

SET @somePATID = '(170282, 221342, 4539)'

SET @someSQL = 'SELECT * FROM OPENQUERY(Relativity, ''Select histpatid, histgroupcode, histdate, histdup, histeffdate from history where histpatid in ' + @somePATID + ''')'

EXEC(@someSQL)

...
Note "Relativity" is my linked server name.
it seems necessary to use the "EXEC" method of execution in order to get the parameterized string to run rather than a direct reference to "OPENQUERY". In the case above I used an "in" clause and provided a set of ID's. But, it could just as well been a single valued comparison. It is up to you to build up a string to pass. If you want to test what is being sent to the "EXEC" just replace EXEC with ...

select @someSQL

and it will show you the string that is built. You can judge for yourself then if the parameter has been embedded in the query correctly.

Also, note that in example I found and rewrote, the original had between 5 and 7 or 6 and 8 single quotes in the spots you see quotes above. If you play with the quotes in matched pairs, i.e. adding 1 or 2 single quotes to each set of quotes around the parameter you will see the effect.
I finally settled on the minimum number in each set where I could get it to form the correct sql string. It happened to be an odd number in each set. In this case 1 and 3 quotes around the parameter.

I certainly don't like the above solution, as it is such a workaround. But, it does work. Let me know if it works out to call an Oracle stored proc.