MSR_GEN_0071 Internal error getting catalogs and schemas Error

[gbez]

Hi,

I have installed the latest release of Cognos Analytics that I could find - 11.0.3. Whenever I try create a data module using an oracle connection as the data source, I get the following error:

MSR_GEN_0071 Internal error getting catalogs and schemas

The connection is configured for JDBC and connects successfully. I have tried all the different versions of the jdbc drivers I could find, but all end up with the same result. I don't have the same problem when connecting to a SQL Server source. It is a 64 bit environment. Please can someone assist?

[MFGF]

Hi,

I have installed the latest release of Cognos Analytics that I could find - 11.0.3. Whenever I try create a data module using an oracle connection as the data source, I get the following error:

MSR_GEN_0071 Internal error getting catalogs and schemas

The connection is configured for JDBC and connects successfully. I have tried all the different versions of the jdbc drivers I could find, but all end up with the same result. I don't have the same problem when connecting to a SQL Server source. It is a 64 bit environment. Please can someone assist?

Hi,

Have you made sure you copied the Oracle jar files to the Drivers folder? JDBCs go in here now, rather than in webapps\p2pd\lib

You could try the steps in the below technote to see if it resolves the problem:

http://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ig_rolap.doc/t_ig_rolap_oracle_connect.html

Cheers!

MF.

[gbez]

Hi MFGF,

Thanks for the response, I followed those steps and still have the same problem.

Has anyone actually managed to get this to work? If so, would you mind sharing what drivers you used?

[MFGF]

Hi MFGF,

Thanks for the response, I followed those steps and still have the same problem.

Has anyone actually managed to get this to work? If so, would you mind sharing what drivers you used?

I haven't tried Oracle, sorry. DB2 and SQL Server work fine for me, but that's no help to you :)

As a sanity check, what happens if you test your data source connection in the Administration console? Does the test work? If so, can you get FM to import data in a DQM framework model?

MF.

[Murphy_Martin]

gbez, did you find a solution to your problem? I am experiencing the same error message.

Thanks,
Murphy

[gbez]

Hi,

Sorry I haven't been active for a while, I still haven't found a solution to this issue. Its getting a bit frustrating now, I need to demonstrate the dashboarding capability to a customer but at this point I cant get it to work at all...

I can test the data source without any issues, and it also works in FM. I am suspecting a driver issue, but I have tried all of them but to no avail. Any help from anyone would be appreciated, thanks in advance.

[gbez]

Sorry, correction. In FM, when running the metadata wizard and connecting to the Oracle data source I am getting the following error:

BMT-IMP-0002 Failed to execute metadata request. jdbcSQLExceptionCAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2016-08-11-08:56:01.705-#1

[MFGF]

Sorry, correction. In FM, when running the metadata wizard and connecting to the Oracle data source I am getting the following error:

BMT-IMP-0002 Failed to execute metadata request. jdbcSQLExceptionCAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2016-08-11-08:56:01.705-#1

Hmmm. Interesting. It's not just a Data Modules issue then. Can you take a look in cogserver.log to see what error message was actually logged?

Also see if this helps:

http://www-01.ibm.com/support/docview.wss?uid=swg21624136

MF.

[gbez]

That didn't help unfortunately. I cant find much in the logs, see attached.

[MFGF]

That didn't help unfortunately. I cant find much in the logs, see attached.

Yup - not much help there!

Let's recap for a moment. Are you sure you don't have multiple ojdbc<x>.jar files in the Drivers folder? You should only have one. If you have multiple, I think Cognos just uses the first one it finds, which is possibly the wrong one. What version of Oracle are you using? If it's 12cR1 you should be using ojdbc7.jar. If it's 11gR2 you should be using ojdbc6.jar. If it's neither of the above you're not going to be able to connect, as these are the only two supported versions for use with Cognos Analytics (CA uses JRE8 and earlier versions of Oracle don't have a JDBC driver that can be used with JRE8).

Can you check the above and let us know?

Cheers!

Mark.

[gbez]

We are using 11gR2 and I have the ojdbc6 driver in the drivers folder. We have both the 32 and 64 bit version of Oracle installed. The strange thing is if I open one of our old FM projects, and run the metadata wizard using the Oracle connection, it retrieves the schemas successfully. Its only if I create a new project from scratch, and then run the metadata wizard on that same Oracle connection that I get the error mentioned above. Its almost like they seem to be using different drivers for some reason?

[MFGF]

We are using 11gR2 and I have the ojdbc6 driver in the drivers folder. We have both the 32 and 64 bit version of Oracle installed. The strange thing is if I open one of our old FM projects, and run the metadata wizard using the Oracle connection, it retrieves the schemas successfully. Its only if I create a new project from scratch, and then run the metadata wizard on that same Oracle connection that I get the error mentioned above. Its almost like they seem to be using different drivers for some reason?

In your old FM project, is it set to use CQM, I wonder? If you fire up the Publish Package wizard, do you see the option "Use Dynamic Query Mode" underneath the "Verify the package before publishing" checkbox?

MF.

[gbez]

Hi,

I think I have found the error that is causing the problem. I reinstalled CA on my local, got it up and running and reproduced the problem. I can configure the jdbc connection and test it successfully. It is only when it is trying to retrieve the metadata for the connection that I get the problem. I found a more detailed error, as you can see in the attached log:

ORA-01882: timezone region not found

I was getting this error before when trying to configure a jdbc connection and fixed it by editing the xqe.config.xml file as per this thread:

http://www-01.ibm.com/support/docview.wss?uid=swg21641476

That sorted out the issue when configuring the jdbc connection, but I seem to be getting the same error now when CA tried to retrieve the metadata for the Oracle connection. I don't know if it uses a different driver when doing that operation? Or if there is somewhere else I need to set the timezone region, but I cant find anything on the web.

[MFGF]

Hi,

I think I have found the error that is causing the problem. I reinstalled CA on my local, got it up and running and reproduced the problem. I can configure the jdbc connection and test it successfully. It is only when it is trying to retrieve the metadata for the connection that I get the problem. I found a more detailed error, as you can see in the attached log:

ORA-01882: timezone region not found

I was getting this error before when trying to configure a jdbc connection and fixed it by editing the xqe.config.xml file as per this thread:

http://www-01.ibm.com/support/docview.wss?uid=swg21641476

That sorted out the issue when configuring the jdbc connection, but I seem to be getting the same error now when CA tried to retrieve the metadata for the Oracle connection. I don't know if it uses a different driver when doing that operation? Or if there is somewhere else I need to set the timezone region, but I cant find anything on the web.

Hmmmm. We're well into uncharted waters here for me, I'm afraid. The technote you linked to says to edit xqe.config.xml (in the configuration folder), but in my copy of this file in Cognos Analytics it says on line 8:

<!--Do not modify this file! This file contains default settings. If you wish to modify these settings, please make a copy of this file and rename it "xqe.config.custom.xml" then make your changes to the new file.-->

Did you change xqe.config.xml or did you take heed of the warning and create xqe.config.custom.xml?

MF.

[Murphy_Martin]

Gbez,

I found the solution for me. I tried a ton of different things with the drivers (plus other things), but the problem was simply I wasn't logging in right. Weird, I know.

So when I connected to my DB2 data server via Cognos Analytics (Manage -> Data servers), I was using the second radio button 'Prompt for Username and Password'. I tested the connection and it succeeded, so I just pressed 'OK' and figured I was connected as I should be (it showed that I was connected to the DB Server and everything.)

Well, turns out, you really have to connect using the fourth radio button 'Use the following saved credentials' and then enter in your Username and Password for your DB.

I know this is sort of a dumb mistake, and it probably isn't the problem you're having, but I thought I'd let you know. I never thought to go back to step 1 (logging in) when everything seemed to have gone so smoothly at this step.

Hopefully that helps you,
Murphy

[gbez]

Thanks Murphy, I already had the fourth radio button so unfortunately that wasn't the problem.

MF, I made the change directly to the file xqe.config.xml.

[gbez]

Thanks for the replies guys, but I have decided to log this with IBM as I suspect it may be a bug. I will let you know what they come back with.

Thanks again

[MFGF]

Thanks for the replies guys, but I have decided to log this with IBM as I suspect it may be a bug. I will let you know what they come back with.

Thanks again

I think that's very wise. Hopefully they can help you figure out how to get things working. Good luck!!

MF.

[Rahul Ganguli]

Gbez,

Any updates from IBM on this?

[the6campbells]

If you are hitting errors with data modules (creation or refresh), currently you would need to access the server and skim the \logs\p2pd messages file for additional error context. Dashboard and data modules services both log to that file. Dynamic Query and the service used by Framework Manager will log to \logs\xqe\... Your systems must place JDBC drivers in the \drivers folder in order to ensure that all the services can load the drivers from that location.

[brian_brooks]

Hi Everyone,
I've seen the same behavior in both Cognos Analytics R3 and R4 now running against SQL 2012. I've confirmed that we only have the sqljdbc4.jar in the \drivers folder. Our configuration is slightly different in that we set the IBM service to run under specified windows credentials and then use the Sign On option of IBM Cognos Windows Service credentials for the server. Again, everything tests fine within the connection screen, however we get the same MSR_GEN_0071 error when trying to pull the catalogs/schema for the database. I worked through a couple of different support people while on release 3 with absolutely no success.

Luckily we were just beginning the dev phase of this product, so it was easy for us to uninstall release 3 and cross our fingers that this would be fixed in release 4. So far, nothing has changed in that regard. Overall, this latest version of Cognos has been a never-ending frustration.

[bus_pass_man]

1.  What information is being logged in p2pd_messages.log when the error occurs?
2.  What information is being logged in the xqe log files when the error occurs?
3.  Are the schemas you're looking for listed in the \wlp\usr\servers\cognosserver\moser\import.xml file?  If so you can edit the file to not screen them out.
4.  Are you able to import metadata into FM or cube designer?
5.  When exactly do you get the error?  When you're trying to see the schema?  When you've chosen a schema and tried to import metadata?

[brian_brooks]

1.  What information is being logged in p2pd_messages.log when the error occurs?
2.  What information is being logged in the xqe log files when the error occurs?
3.  Are the schemas you're looking for listed in the \wlp\usr\servers\cognosserver\moser\import.xml file?  If so you can edit the file to not screen them out.
4.  Are you able to import metadata into FM or cube designer?
5.  When exactly do you get the error?  When you're trying to see the schema?  When you've chosen a schema and tried to import metadata?

1. attached p2pd. Basically says that the driver is not configured for integrated authentication
2. nothing new logged in xqe log file when error occurs. There's a dq_verbosegc_xx file but it doesn't contain anything of interest.
3. I edited that file and removed the sql portion of the xml. The after restarting the services the error still shows up for no schema/catalog available
4. I haven't tried this yet, I'm just trying to get some basic functionality off the ground. Once I can verify this I'll get to FM.
5. I get the error when clicking Manage > Data Server Connections > select the Connection of choice and it then tries to pull Schema information. From the viewer/UI perspective, it's the 3rd tier dialog screen in the data server connections window when clicking directly on the connection name.

This happens consistently either with existing or new connections. And has happened in 2 different versions of the analytics product. Granted, we're trying to connect in a slightly different way, a way that works perfectly well in 10.2.2. So if this is a configuration issue that needs to be changed, great! I just haven't seen any documentation or been able to have anyone from support be able to walk me through how to fix this. And it sounds like others are having similar albeit varied issues.

[bus_pass_man]

You have a problem with the sqljdbc_auth.dll. I believe that you need it in the drivers directory in addition to the other file.

[10/6/16 8:36:51:041 CDT] 00002ad6 com.microsoft.sqlserver.jdbc.internals.AuthenticationJNI     W Failed to load the sqljdbc_auth.dll cause : sqljdbc_auth (Not found in java.library.path)
[10/6/16 8:37:05:698 CDT] 00002ad6 tform.moser.core.metadata.provider.jdbc.JDBCMetadataProvider E com.ibm.cognos.jdbc.adaptor.sqlexception.SQLCognosInvalidLogonException: This driver is not configured for integrated authentication.

Here's some postings that I found that might help.  There's 7 posts on cognoise alone which have that dll mentioned so there might be lots more resources you can use.

http://www.cognoise.com/index.php/topic,25669.msg81370.html#msg81370

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page529.html

http://stackoverflow.com/questions/11707056/no-sqljdbc-auth-in-java-library-path

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1c1a1313-057d-46d4-9ca7-82fc4f23a150/cant-get-integratedsecurity-with-jdbc-driver?forum=sqldataaccess

[brian_brooks]

Thanks, bus_pass_man. I've got the sqljdbc_auth.dll file in the \drivers directory and have restarted the service already with no luck. I've also tried modifying the databaseDriverLocations.properties in \v5dataserver to point to the jdbc and jdbc_auth files. Neither of these has resolved the problem, but I think they're definitely along the right path!