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.