I am using CR9 and CE9 connecting to an Informix 9.4 database using the Crystal-supplied Informix driver. Outside of CR, when we are issuing queries from the query environment of Informix (using the server side utility dbaccess), we set two options to improve performance of our queries:
set isolation dirty read;
set pdqpriority XX; (where XX is a priority #)
I would like to be able to set these options in the ODBC connection for each and every report I run against the database. We have tried various strategies but have thus far not been able to figure out how to do from within Crystal Reports.
I wrapped the two statements into a stored procedure and tried calling the stored procedure from a subreport in the report header. Doesn't work. I have tried creating a SQL command such as:
set isolation dirty read;
set pdqpriority 20;
select * from mytable;
and this results in the error message "Cannot use a select or any of the database statements in a multi-query prepare".
I have spoken to Crystal Decisions support and they had no solution.
Not being able to set these options severely hampers performance of our reports. Surely there's got to be a way to do it. I'm hoping someone out there is doing something similar, whether on Informix or another engine.
What it boils down to is we need these 2 statements sent to the db engine before the query is issued to the database.
Thanks,
--Bearden
set isolation dirty read;
set pdqpriority XX; (where XX is a priority #)
I would like to be able to set these options in the ODBC connection for each and every report I run against the database. We have tried various strategies but have thus far not been able to figure out how to do from within Crystal Reports.
I wrapped the two statements into a stored procedure and tried calling the stored procedure from a subreport in the report header. Doesn't work. I have tried creating a SQL command such as:
set isolation dirty read;
set pdqpriority 20;
select * from mytable;
and this results in the error message "Cannot use a select or any of the database statements in a multi-query prepare".
I have spoken to Crystal Decisions support and they had no solution.
Not being able to set these options severely hampers performance of our reports. Surely there's got to be a way to do it. I'm hoping someone out there is doing something similar, whether on Informix or another engine.
What it boils down to is we need these 2 statements sent to the db engine before the query is issued to the database.
Thanks,
--Bearden