:( OPENQUERY Problem passing parameters

[mkintner]

Passing Parameters into a SQL Query using OPENQUERY.

Below is the syntax for using OPENQUERY:

Code: Select all

SELECT * FROM OPENQUERY([AS400_Test], 'Select * From QS36FTEST.ZBBORDH') RS_ORHD

OPENQUERY is used through SQL Server to OPEN other ODBC sources within the SQL Server however when using OPENQUERY because of the single quotes the parameters do not work inside of the OPENQUERY. It appears that your component will not take into effect the single quotes. Is there a work around to using parameters with OPENQUERY?

Example:

Code: Select all

SELECT * FROM OPENQUERY([AS400_Test], 'Select * From QS36FTEST.ZBBORDH WHERE TEMP=:inTEMP') RS_ORHD

It appears your component does not reconize the parameter :inTEMP and is because the parameter is inside the single quotes of the OPENQUERY statement. Do you have a solution for this. Current we are re-writing each SQL statement on the fly however would like to use parameters.

Can you help?

[dhastas]

That would be a great feature I have experienced this several times
and would love to have a solution

[Dimon]

SDAC does not allow using parameters inside of SQL Statement of OPENQUERY, because it is a parameter of OPENQUERY function oneself.

[mkintner]

This may be true, however why couldn't the DevART's SQL component have a feature to handle single quotes inside of the OPENQUERY? The component could have a feature request to reconize OPENQUERY and then be able to handle the paramaters passed within the OPENQUERY.

This option is becoming more and more needed with the advent of SQL server being able to handle multiple systems within the SQL environment. The feature would make DevART components more powerful to handling multiple database systems not to mention convience.

I would only hope that DevART takes some sort of action vs just ignoring the issue by not trying to providing some sort of workable solution.

[bajaluna]

You know you are correct this would be a great feature!

I use both the SQL and Oracle through Adelphia Communications or now Time Warner and we have requested this feature as well but with no luck in supporting a change.

A feature like this could be done by DevART but they only seem to build requests they deem useful. Its too bad because we use there component in alot of our applications. I'll send a private message to you on who we use to perform this feature, they built this feature for us in there last update and works greats.

Hopefully others will vote your poll and I will pass the word at Time Warner.

Good luck...

[dhastas]

That would be great if you have a solution to this as it would be
very useful. It would be GREAT if you could share this solution with all.

[Dimon]

Restriction of using parameters inside of OPENQUERY is a peculiarity of SQL Server work and doesn't depend on SDAC.
SQL Server 2005 Books Online in remarks to OPENQUERY statement say:

OPENQUERY does not accept variables for its arguments.