Error message: MSR-GEN-0071 internal error getting catalogs and schemas

[superman99]

I am using CA 11.0.11 but when I try to create a data module using MS SQL Server, I am getting an error even though the connection is successful. I have jdbc4.jar in the drivers' folder and Sqljdbc_auth.dll under bin64 but still I am unable to see the schemas. I am using MS SQL Server 2014.

I read all the post related to this issue and I am doing everything possible but still no luck. When I try to use FM, connect to the database I am able to see the schemas.

Appreciate your prompted response.

Thanks

[MFGF]

I am using CA 11.0.11 but when I try to create a data module using MS SQL Server, I am getting an error even though the connection is successful. I have jdbc4.jar in the drivers' folder and Sqljdbc_auth.dll under bin64 but still I am unable to see the schemas. I am using MS SQL Server 2014.

I read all the post related to this issue and I am doing everything possible but still no luck. When I try to use FM, connect to the database I am able to see the schemas.

Appreciate your prompted response.

Thanks

Hi,

How have you defined the data source connection to SQL Server? Is it set up as a Data Server connection? If so, can you go back to the properties of the connection (on the Connections tab), choose the Schemas tab, and use the ellipsis to the right of your schema and select the "Load metadata" option?

Cheers!

MF.

[superman99]

Thanks for your prompted response.

When I click on the schema, I get the error "MSR_GEN_0071 Internal error getting catalogs and schemas"

[MFGF]

Thanks for your prompted response.

When I click on the schema, I get the error "MSR_GEN_0071 Internal error getting catalogs and schemas"

Hi,

That sounds to me like an issue with the JDBC driver you are using. Can you confirm you have copied sqljdbc42.jar into the Drivers folder in your CA install location?

Cheers!

MF.

[superman99]

I tried the following drivers one by one and none of them worked. After putting one at a time, I restarted Cognos services.

jdbc4
jdbc41
jdbc42

[MFGF]

I tried the following drivers one by one and none of them worked. After putting one at a time, I restarted Cognos services.

jdbc4
jdbc41
jdbc42

Just to be clear, when you refer to jdbc42 do you mean you copied in the sqljdbc42.jar file to the Drivers folder? What executable did you run to unpack the driver? sqljdbc_4.2.6225.100_enu.exe?

MF.

[superman99]

Here is what I exactly did:
I installed the JDBC driver from https://www.microsoft.com/en-sa/download/details.aspx?id=11774 the following files get installed "sqljdbc_6.0.8112.200_enu.tar" once I untar it I see the following folder "sqljdbc_6.0 -> enu -> jre8" that contains "sqljdbc42.jar" file that I copied and put under driver directory where I have cognos installed.

While installing, I do see the following files which I can select to be installed "license60.txt & sqljdbc_6.0.8112.200_enu.exe" that I did not install.

[bus_pass_man]

IIRC you need to restart the server for the jdbc drivers to be picked up.  Make sure you have only one driver from a particular vendor in the drivers directory as you can get conflicts.

Check the log files -- cognosserver.log if you have a recent version R6 (I think) or later or p2pd .log if earlier and see what additional messages are recorded there.  It will have more information.

What are the permissions on the data source?  What permissions does the entity you're logged in as have as part of his membership of different users, groups, and roles?

What capabilities do you have?  In particular import metadata.

Are you able to import the metadata in FM?

Just to confirm you not able to load the metadata.

If you choose load options and click on the tables tab can you see things?

[superman99]

Yes, I have restarted Cognos services once I add in the JDBC driver to the driver directory and I only have one driver.

I am able to connect through FM and see the schemas, tables, columns. I have created a package using FM with the same schema that I am trying to access using Data Server Manager.

I do not see the option to load metadata because as soon as I click on schema I get an error which is posted in the above post.

It is really weird but I am not able to figure about. I did check the p2pd messages log but do not find much information in it.

[bus_pass_man]

It's nice you can see the objects in FM.  Can you actually import them and, for that matter, use them?

What do you see in the cognosserver.log?

It would be helpful to you if you answer the questions about permissions etc. too.

[superman99]

I even tried with the Dba permission as well but it didn't work so because of time I started creating the dashboard using package.

I read the cognosserver.log and what I see in it is a bit strange. There are 14 different instances in the development database and in the logs, it shows that I do not have access to an instance which I am not even using for example, I am using instance name 'Cognos" and there are 13 more different instances starting from ABC, DEF, GHI, KHL, etc, the dba gave me access to ABC then it starts complaining about the next one that I do not have access which DEF and so on. Why Cognos can not directly connect to the instance that I am defining "Cognos" that what I do not get.

[bus_pass_man]

Do you have the database that you need to connect to specified in the data source connection's jdbc string? 

I think by 'dba permission' you're speaking about the data base's permissions.   I wasn't referring to that but the permissions on the data source connection (i.e. the stuff in the data source connection that you set up in CA). You don't need much beyond read access to the data base itself.  You need write access as you're adding the metadata of the data base into your Cognos environment.

[RTheOpsGuy]

Regarding this error message, for 32-bit Oracle (11gR2) DB connections ojdbc6.jar should be correct right? I am facing the same issue unfortunately and can't figure out what it could be.
Had ojdbc5 and 6 in simultanously before, I thought removing 5 would fix my issue but it didn't. ojdbc6.jar supports jdbc 4.0 which is required for the Schemas tab IIRC.

[superman99]

Finally, I gave up due to a deadline that I have to meet. I created a FM package and using that to develop dashboards/reports.