Odd ODBC issue with DB2

[Former Member]

Post Author: Nicholas

CA Forum: Data Connectivity and SQL

We have a group of 100 or so reports that are being reviewed due to a massive upgrade made to a database. These reports were created in Crystal 8-ish all the way up to XI.We had someone change the DB2 library in an ODBC connection to the new library so they could open the reports, run them, and keep a list of which one's need to be updated. The problem we just ran in to, is that some of the reports are still pulling from the old library. When you click "Show SQL Query" it has the old library listed in the SQL.From what I've seen, the reports with parameters seem to show the old library, those without parameters are showing the correct library that the ODBC connection was changed to.The only way I've been able to get the report to pull from the correct library was to manually update the tables. Updating at the database level didn't work.Has anyone run into an issue like this?The plan was to change the ODBC settings so the reports would pull from the new database. If we have to manually update each table in each report, this will take 10 times longer to do.Any suggestions would be appreciated.

[Former Member]

Post Author: Nicholas

CA Forum: Data Connectivity and SQL

synapsevampire:Ummm, change the ODBC connection?

What does that mean? I thought you had already changed the ODBC connection.

Crystal doesn't seem to catch new ODBC settings if you change the library. It will; however, if you create a new ODBC connection and point it to the new one.

synapsevampire:Seems odd that it wouldn't refresh the datasource using verify database, that's the intent of it. Then again it might just be aliased at that, guess you'll need to use Set Location or perhaps a 3rd party product such as Report Miner which can change multiple reports at once.

There probably isn't "free" software that will allow you to change connections on multiple reports is there? I'm sure the company I'm working for isn't going to cough up the money to buy Report Miner.

[Former Member]

Post Author: GraemeG

CA Forum: Data Connectivity and SQL

synapsevampire: Seems odd

Thats not exactly how I said it after I'd done 60-or so reports, I can tell you! 🐵

I haven't been able to find anything on the subject here, on the IBM website, or via Google - perhaps thats just the way it is.

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

Ummm, change the ODBC connection?

What does that mean? I thought you had already changed the ODBC connection.

Seems odd that it wouldn't refresh the datasource using verify database, that's the intent of it. Then again it might just be aliased at that, guess you'll need to use Set Location or perhaps a 3rd party product such as Report Miner which can change multiple reports at once.

-k

[Former Member]

Post Author: Nicholas

CA Forum: Data Connectivity and SQL

synapsevampire:Try selecting Database->Verify Database, then check the SQL.

So far using "Verify Database" has done nothing in effort to refresh the library that's showing up in the SQL statement.

I either have to use "Set Datasource Location..." and update the connected objects (tables) manually, or change the ODBC Datasource. It seems that Crystal will only refresh the library from the ODBC connection settings when you use either of these methods.

GraemeG:I truely hope that someone says 'Aha, here's how you do it" because is soul destroying stuff going through each report changing the datasource during every step of our project rollout.

I hear ya!

[Former Member]

Post Author: GraemeG

CA Forum: Data Connectivity and SQL

As far as I know, changing the SQL default library in the ODBC connection doesn't make a jot of difference... we have a similar issue and are having to manually set the datasource location to look at the new library for all our reports. This forces a regeneration of the SQL.

The problem appears to be caused by the fact that the catalogue (library list) is not refreshed every time you run the report and the library name is part of the generated SQL statement. I have tried various things in the 'Options' of the report (Database tab) with no joy.

I truely hope that someone says 'Aha, here's how you do it" because is soul destroying stuff going through each report changing the datasource during every step of our project rollout.

[Former Member]

Post Author: synapsevampire

CA Forum: Data Connectivity and SQL

Try selecting Database->Verify Database, then check the SQL.

-k