Topic
4 replies Latest Post - ‏2013-01-12T12:20:07Z by DanielWagemann
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

Pinned topic I need help retrieving SQL from an .IMR

‏2013-01-11T16:42:16Z |
I posted this in the Cognos 7 forum with no luck, so I'm hoping someone here can help.

Our company used to use Cognos to develop reports, but hasn't for several years. I need to retrieve the SQL from some of the existing reports. We don't have a Cognos administrator, nor anyone who knows much about the product. We are running Cognos 7, Version 2.

I can get in to Cognos, and can open a catalog and connect. I can also open .IMR files and run the reports.

My problem is that when I go to Report/Query to get the SQL, "Query" is always disabled, so I can't get at the SQL.

Any ideas as to how I can get at the SQL?
Updated on 2013-01-12T12:20:07Z at 2013-01-12T12:20:07Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: I need help retrieving SQL from an .IMR

    ‏2013-01-11T16:52:47Z  in response to SystemAdmin
    There are two ways, you can save the IMR report as an IQD and it would give you the SQL or enable a the following trace:

    TRACE
    output=c:\<file name>
    flags=1111111111

    In one of the ini file specific to your connection.
    cogdmod.ini for ODBC
    cogdmor.ini for Oracle
    cogdmd2.ini for DB2
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: I need help retrieving SQL from an .IMR

      ‏2013-01-11T17:40:49Z  in response to DanielWagemann
      Excellent. The .IQD option works. Now it gets harder. There is one .IMR that has errors (I assume in the SQL), so I can't run it. Any thoughts on this one?
      • SystemAdmin
        SystemAdmin
        15496 Posts
        ACCEPTED ANSWER

        Re: I need help retrieving SQL from an .IMR

        ‏2013-01-11T18:48:53Z  in response to SystemAdmin
        More info.

        In some of the catalogs, I can go in with User Class "Developer", which enables the "Query" selection. But in the particular catalog I need to use, "Developer" is not in the drop down, so "Query" is disabled.
      • DanielWagemann
        DanielWagemann
        411 Posts
        ACCEPTED ANSWER

        Re: I need help retrieving SQL from an .IMR

        ‏2013-01-12T12:20:07Z  in response to SystemAdmin
        For the report that does not run, you may need to use the ini file trace. If the error allows for a SQL prepare you can get the SQL otherwise you will need to fix the problem with the report.

        Your catalog could be distributed, you would need the main catalog and or the creator password.