In my current job, I have a MySQL database server that I am responsible for. We also run some reporting through Cognos against this server. I ran across a problem while testing reports on an upgrade to Cognos 10. I thought I would share the solution here in case it helps someone else out someday.
I kept running into error messages while testing the Cognos reports that hit the MySQL database:
- RQP-DEF-0177 An error occurred while performing operation ‘sqlOpenResult’ status=’-28′.
- UDA-SQL-0114 The cursor supplied to the operation “sqlOpenResult” is inactive.
- UDA-SQL-0283 Metadata describing <column name> does not match results from the database.
I then opened up Cognos Framework Manager to look at the package. The weird thing is that I was able to run a Test Sample and see data return in Framework Manager. However the same data item was throwing errors in Report Studio. When I looked at the data items in Framework Manager, they had a data type of nVarChar.
Time to bring in the big guns
I started working with our Cognos support group to figure out what was going on. They were able to edit the definition of one of the data queries in Framework Manager. When the updated the data item, the data type of the text columns changed from “nVarChar” to “Character Length 16”. Once they made the change and updated the package, the reports started running again in Report Studio.
Woohoo, problem solved!
Oh wait, I spoke too soon…I went back in to Framework Manager to repeat our support group’s steps against other query items to update the whole package. Unfortunately when I updated any of the data queries, the data type kept changing back to “nVarChar” and the reports threw errors again. I even tried to re-update the data item that our support group fixed. When I made my edit, the reports broke again.
Long story short, and after 2 weeks and plenty of headaches….I had a different version of the MySQL ODBC driver installed on my PC from what our Cognos support group did. I changed the driver version from 5.1 to 3.51, and finally it worked! I figured that the newer driver would be better, but apparently Cognos 10 Framework Manager has a problem with the MySQL ODBC 5.1 driver, and needs the 3.51 driver instead.
With the older driver installed on my system, I was able to update the Framework Manager package and then pull back data error free in Report Studio.
One other thing of note: after I update the query subject in the Physical Layer in Framework Manager, the links broke in the Presentation layer. I had to go back into the Presentation Layer to update the links there as well.