Run this on your IdM Report Server if you Can't view reports.

Discussion created by Chris_Thomas

on Dec 7, 2012
Latest reply on Mar 24, 2016 by Ashok kumar Muthu

Like • Show 1 Like1
Comment • 11

For a very brief summary of the problem / solution, please watch this 3 min video[color]. [size]

If you'd just like to solve your problem without understanding it, simply download this zip file, copy the contents to your report server desktop and run Simplify.vbs out cmd prompt launched as administrator.[color]. [size]

The fact that the IdM tools directory publishes the 32 bit version of the registry manipulation ONLY, which is required for the report server regardless of the platform chosen, generates a plethora of support issues that could easily be avoided.

A VB scripted solution has been published onto MyCA communities forum in order to try to mitigate incoming volume on this topic until a more permanent solution is deployed. (possibly simply deploying the attached script into the ReportServerTools directory of IM, which would replace the existing registry scripts that don’t work on 64bit platform)

If you're getting an invalid database connection error when trying to view reports, I suggest you run the attached WSH script (as local Administrator on the Report Server) to correct your problem.

'Christopher Ryan Thomas; Unify and Simplify registry deployment for IdM report server. Set WshShell = CreateObject("WScript.Shell") OsType = WshShell.RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\PROCESSOR_ARCHITECTURE") If OsType = "x86" then wscript.echo "Windows 32bit system detected... Applying 32bit reg key value pair" WSHShell.RegWrite "HKLM\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes" elseif OsType = "AMD64" then wscript.echo "Windows 64bit system detected... Applying 64bit reg key value pair" WSHShell.RegWrite "HKLM\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes" end if

Documentation is also incorrect!

@HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports

The above line should also include DatabaseOptions after Crystal Reports. Putting the MergeConnectionsProperties in the wrong location will result in the failure to view reports because it's unable to communicate with the database.

[color=#f30808][font=Comic Sans]If you're getting an invalid database connection error when trying to view reports, I suggest you run the attached WSH script (as local Administrator on the Report Server) to correct your problem.[size=11][color][size][font]

Please post with any questions or concerns.
Thank you.
Regards,

Chris Thomas
CA Technologies
Principal Support Engineer
IdentityMinder Reporting Expert
Tel: +1-631-342-4360
Chris.Thomas@ca.com

Outcomes

ponvi02 Mar 13, 2013 9:31 AM

Hi ,

Have configured reports (BO 3.3 ) in IM R12.5 SP15. After requesting the reports, in View my reports , the status is " Failed to open the connection.~tmp7eaa76ff381d990.rpt" .
Unable to view the reports because of this.

Report server version . 12.5_1265
O.S : RHEL 6.2 64 bit

we have tried all the posible configuration and checkings, unable to resolve this issue.

Any idea what could be the reason for this issue?

Thanks,
Vidhya.

Actions

Chris_Thomas

@ ponvi02 on Mar 28, 2013 4:05 PM

I just had the same problem, and this solved it for me.

Attachments

Reg.pdf51.9 KB

Actions

Chris_Thomas

@ Chris_Thomas on May 1, 2013 1:28 PM

[color=#1909f4]I've updated the script a bit for windows deployments...
- it will update the registry
- deploy the jar files
- restart the SIA
[color]

99% of the time this is what causes this issue.

'Christopher Ryan Thomas
'https://communities.ca.com/web/chris_thomas/myca
'Unify and Simplify registry deployment for IdM report server. 

dim WshShell, installDir
Set WshShell = CreateObject("WScript.Shell")
OsType = WshShell.RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\PROCESSOR_ARCHITECTURE")
If OsType = "x86" then 

wscript.echo "Windows 32bit system detected... Applying 32bit reg key value pair"

WSHShell.RegWrite "HKLM\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes"

installDir = WshShell.RegRead("HKLM\SOFTWARE\Business Objects\Suite 12.0\Shared\SuiteInstallDir")
elseif OsType = "AMD64" then

wscript.echo "Windows 64bit system detected... Applying 64bit reg key value pair"

WSHShell.RegWrite "HKLM\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes"

installDir = WshShell.RegRead("HKLM\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Shared\SuiteInstallDir")
end if 

dim fso, libs
Set fso = CreateObject("Scripting.FileSystemObject")
libs = installDir & "common\4.0\java\lib\"
fso.CopyFile ".\*.jar", libs,TRUE

Const ForReading=1
Const ForWriting=2
dim folder, filePath, myFile, myTemp, myLen, myString, newSize
folder = installDir & "\common\4.0\java\"
filePath = folder & "CRConfig.xml"
Set myFile = fso.OpenTextFile(filePath, ForReading, True)
Set myTemp= fso.OpenTextFile(filePath & ".tmp", ForWriting, True)
Do While Not myFile.AtEndofStream
 myLine = myFile.ReadLine
 If InStr(myLine, "<Classpath>") Then

 myLen = Len(myLine)

 newSize = myLen - 15

 myString = Right(myLine, newSize)

 myLine = "<Classpath>" & libs & "sqljdbc.jar;" & libs & "ojdbc14.jar;" & myString
 End If

 myTemp.WriteLine myLine
Loop
myFile.Close
myTemp.Close
fso.DeleteFile(filePath)
fso.MoveFile filePath&".tmp", filePath

strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
Dim strService
' Values set
strService = " BOE120SIA" & strComputerName

'Option Explicit
Dim intShortSleep, intLongSleep

' Values set
strService = " BOE120SIA" & strComputerName
intShortSleep = 2000
intLongSleep = 9900

' Cmd prompt opened 
WshShell.Run "cmd" 
Wscript.Sleep intShortSleep

' Service stopped with 'Net' command
WshShell.SendKeys "net stop" & strService 
Wscript.Sleep intShortSleep
WshShell.SendKeys "{Enter}"
Wscript.Sleep intLongSleep

' Service started with 'Net' command
WshShell.SendKeys "net start" & strService 
Wscript.Sleep intShortSleep
WshShell.SendKeys "{Enter}"
Wscript.Sleep intLongSleep

' Cmd prompt exited
WshShell.SendKeys "Exit"
Wscript.Sleep intShortSleep
WshShell.SendKeys "{Enter}"

Wscript.Echo strService & " service restarted "
WScript.Quit

Attachments

Simplify IT.zip1.9 MB

Actions

ponvi02 @ Chris_Thomas on Apr 1, 2013 6:13 AM

Thank you Chris for your reply.

we have 2 BO servers on different Linux machine, one worked and another did nto work so i happened to submit the post.

one of my team member worked on this and found that BO server unable to contact IM's db MSSQL server, we had to add the SQL machine hostname in hosts file and rerun the registry.
View reports worked after this.

Thanks,
vidhya.

Actions

Chris_Thomas

@ ponvi02 on Apr 30, 2013 8:52 PM

It's worth noting that the error has manifested itself again with later versions of Identity Manager and possibly IdentityMinder.
Version 12.5 SP14 has released an updated sqljdbc.jar file, which if copied over to the report server lib directory will result in the same "failed to open the connection" problem. I've since reverted the jar file packaged within the Simplify IT.zip file to include the previous version of the JAR.

[list]
[*] sqljdbc.jar, size 584207 bytes, (driver from SP14 install - doesn't work)
[*] sqljdbc.jar, size 583286 bytes, (previous driver, works fine)
[list]

[size=7]***[color=#fc0d0d]In SP14, the sqljdbc.jar was upgraded from from 1.2 to 4.0. and 4.0 version of the JAR currently does not work with IdM Reporting, but I'm in the process of raising that with sustaining engineering.[color][size]

Actions

Razvan_Dumitriu @ Chris_Thomas on Apr 25, 2013 7:16 AM

Chris, thank you for your update in regards to SP14!

I managed to fix this way my problem, and also notified the other guys:

100707113

I have been pulling my hair out why it is not working as on the test env (SP13) it worked but when trying to go live with SP14 it wasn't. Didn't cross my mind it is the sqljdbc.jar file.
Also notified TechPubs about errors in the documentation and they suggested it is not related to them and we should contact support...

Actions

bl8cz @ Chris_Thomas on May 2, 2013 8:23 AM

Thank you very much Crhis! This took about 3 days of my life...

Actions

Chris_Thomas

@ bl8cz on May 24, 2013 4:01 PM

Just an FYI, I'm noticing that customers are applying the wrong BIAR file, which also results in this error. I'm planning on extending the script to also complete the biar import process, but it will need to be a bit more dynamic.

For now, please consider the following; the biar file is based on the selection you made for the audit and snapshot databases NOT the selection you made for the CMS database. Please see this diagram for a simplified representation of the database architecture. I.E. if you decided to use MSSQL for your IdM object store, audit and snapshot repository and decided to use MySQL for you report server CMS database, you would select the Microsoft SQL version of the BIAR, NOT the MySQL version. (/CA/IdentityManager/IAM_Suite/IdentityManager/tools/imrexport/ReportDefinitions/IM Standard Reports/Ms-SQL_Reports/ms-sql_reports.biar)

Actions

Chris_Thomas

@ Chris_Thomas on Jul 25, 2013 9:38 PM

Please note all of the above is IdentityMinder specific, but am noticing that this problem affects other CA products, which interface with the report server as well.

In case you're still having issues you may want to implement tracing on the job server to try to isolate what might be causing the issue.

The job server processes the report requests, but it then spawns off a JobServerChild process to do the actual request - and it is this where I am guessing the connection is failing.

There are two ways to get logging on BO, either via _trace.ini files or via the CMC (Central Management Console).

a) If via CMC then
Add the following to properties of Servers/"Crystal Report Job Server"
-type OUTPROC -trace xs -reqtrace on -sdktrace on -crpetrace 7

b) If via trace.ini files taking template from \SC\CommonReporting3\BusinessObjects Enterprise 12.0\logging\logConfig\BOtrace.ini

create JobServerClient_trace.ini
Give setting something similar to :

active = true;
importance = xs;
alert = true;
severity = ' ';
keep = false;
size = 100 * 1000;

Then place JobServerChild_trace.ini in directory :
\SC\CommonReporting3\BusinessObjects Enterprise 12.0\win32_x86

Restart the JobServer service.

For a comprehensive guide on BusinessObjects 3.1 debug and tracing, please see this document.

Actions

Chris_Thomas

@ Chris_Thomas on Oct 22, 2014 7:17 PM

If you receive registry key not found, please make sure you're running the simplify.vbs from a cmd shell, which was "Run As Administrator"

Actions

Ashok kumar Muthu

Mar 24, 2016 2:04 PM

This is really a helpful tip, I had similar issue on the Linux server fixed it for me. Below are the steps for Linux servers:

Integrated Documents

Actions

11 Replies

ponvi02 Mar 13, 2013 9:31 AM
Hi ,

Have configured reports (BO 3.3 ) in IM R12.5 SP15. After requesting the reports, in View my reports , the status is " Failed to open the connection.~tmp7eaa76ff381d990.rpt" .
Unable to view the reports because of this.

Report server version . 12.5_1265
O.S : RHEL 6.2 64 bit

we have tried all the posible configuration and checkings, unable to resolve this issue.

Any idea what could be the reason for this issue?

Thanks,
Vidhya.
Like • Show 1 Like1
Actions
- Chris_Thomas @ ponvi02 on Mar 28, 2013 4:05 PM
  I just had the same problem, and this solved it for me.
  Attachments
  - Reg.pdf51.9 KB
  Like • Show 2 Likes2
  Actions
  - Chris_Thomas @ Chris_Thomas on May 1, 2013 1:28 PM
    [color=#1909f4]I've updated the script a bit for windows deployments...
    - it will update the registry
    - deploy the jar files
    - restart the SIA
    [color]
    
    99% of the time this is what causes this issue.
    
    'Christopher Ryan Thomas 'https://communities.ca.com/web/chris_thomas/myca 'Unify and Simplify registry deployment for IdM report server. dim WshShell, installDir Set WshShell = CreateObject("WScript.Shell") OsType = WshShell.RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\PROCESSOR_ARCHITECTURE") If OsType = "x86" then wscript.echo "Windows 32bit system detected... Applying 32bit reg key value pair" WSHShell.RegWrite "HKLM\SOFTWARE\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes" installDir = WshShell.RegRead("HKLM\SOFTWARE\Business Objects\Suite 12.0\Shared\SuiteInstallDir") elseif OsType = "AMD64" then wscript.echo "Windows 64bit system detected... Applying 64bit reg key value pair" WSHShell.RegWrite "HKLM\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\MergeConnectionProperties", "Yes" installDir = WshShell.RegRead("HKLM\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Shared\SuiteInstallDir") end if dim fso, libs Set fso = CreateObject("Scripting.FileSystemObject") libs = installDir & "common\4.0\java\lib\" fso.CopyFile ".\*.jar", libs,TRUE Const ForReading=1 Const ForWriting=2 dim folder, filePath, myFile, myTemp, myLen, myString, newSize folder = installDir & "\common\4.0\java\" filePath = folder & "CRConfig.xml" Set myFile = fso.OpenTextFile(filePath, ForReading, True) Set myTemp= fso.OpenTextFile(filePath & ".tmp", ForWriting, True) Do While Not myFile.AtEndofStream myLine = myFile.ReadLine If InStr(myLine, "<Classpath>") Then myLen = Len(myLine) newSize = myLen - 15 myString = Right(myLine, newSize) myLine = "<Classpath>" & libs & "sqljdbc.jar;" & libs & "ojdbc14.jar;" & myString End If myTemp.WriteLine myLine Loop myFile.Close myTemp.Close fso.DeleteFile(filePath) fso.MoveFile filePath&".tmp", filePath strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" ) Dim strService ' Values set strService = " BOE120SIA" & strComputerName 'Option Explicit Dim intShortSleep, intLongSleep ' Values set strService = " BOE120SIA" & strComputerName intShortSleep = 2000 intLongSleep = 9900 ' Cmd prompt opened WshShell.Run "cmd" Wscript.Sleep intShortSleep ' Service stopped with 'Net' command WshShell.SendKeys "net stop" & strService Wscript.Sleep intShortSleep WshShell.SendKeys "{Enter}" Wscript.Sleep intLongSleep ' Service started with 'Net' command WshShell.SendKeys "net start" & strService Wscript.Sleep intShortSleep WshShell.SendKeys "{Enter}" Wscript.Sleep intLongSleep ' Cmd prompt exited WshShell.SendKeys "Exit" Wscript.Sleep intShortSleep WshShell.SendKeys "{Enter}" Wscript.Echo strService & " service restarted " WScript.Quit
    Attachments
    Simplify IT.zip1.9 MB
    Like • Show 2 Likes2
    Actions
    - ponvi02 @ Chris_Thomas on Apr 1, 2013 6:13 AM
      Thank you Chris for your reply.
      
      we have 2 BO servers on different Linux machine, one worked and another did nto work so i happened to submit the post.
      
      one of my team member worked on this and found that BO server unable to contact IM's db MSSQL server, we had to add the SQL machine hostname in hosts file and rerun the registry.
      View reports worked after this.
      
      Thanks,
      vidhya.
      Like • Show 2 Likes2
      Actions
      - Chris_Thomas @ ponvi02 on Apr 30, 2013 8:52 PM
        It's worth noting that the error has manifested itself again with later versions of Identity Manager and possibly IdentityMinder.
        Version 12.5 SP14 has released an updated sqljdbc.jar file, which if copied over to the report server lib directory will result in the same "failed to open the connection" problem. I've since reverted the jar file packaged within the Simplify IT.zip file to include the previous version of the JAR.
        
        [list]
        [*] sqljdbc.jar, size 584207 bytes, (driver from SP14 install - doesn't work)
        [*] sqljdbc.jar, size 583286 bytes, (previous driver, works fine)
        [list]
        
        [size=7]***[color=#fc0d0d]In SP14, the sqljdbc.jar was upgraded from from 1.2 to 4.0. and 4.0 version of the JAR currently does not work with IdM Reporting, but I'm in the process of raising that with sustaining engineering.[color][size]
        Like • Show 2 Likes2
        Actions
        Razvan_Dumitriu @ Chris_Thomas on Apr 25, 2013 7:16 AM
        Chris, thank you for your update in regards to SP14!
        
        I managed to fix this way my problem, and also notified the other guys:
        
        100707113
        
        I have been pulling my hair out why it is not working as on the test env (SP13) it worked but when trying to go live with SP14 it wasn't. Didn't cross my mind it is the sqljdbc.jar file.
        Also notified TechPubs about errors in the documentation and they suggested it is not related to them and we should contact support...
        Like • Show 1 Like1
        Actions
        bl8cz @ Chris_Thomas on May 2, 2013 8:23 AM
        Thank you very much Crhis! This took about 3 days of my life...
        Like • Show 1 Like1
        Actions
        Chris_Thomas @ bl8cz on May 24, 2013 4:01 PM
        Just an FYI, I'm noticing that customers are applying the wrong BIAR file, which also results in this error. I'm planning on extending the script to also complete the biar import process, but it will need to be a bit more dynamic.
        
        For now, please consider the following; the biar file is based on the selection you made for the audit and snapshot databases NOT the selection you made for the CMS database. Please see this diagram for a simplified representation of the database architecture. I.E. if you decided to use MSSQL for your IdM object store, audit and snapshot repository and decided to use MySQL for you report server CMS database, you would select the Microsoft SQL version of the BIAR, NOT the MySQL version. (/CA/IdentityManager/IAM_Suite/IdentityManager/tools/imrexport/ReportDefinitions/IM Standard Reports/Ms-SQL_Reports/ms-sql_reports.biar)
        Like • Show 0 Likes0
        Actions
        Chris_Thomas @ Chris_Thomas on Jul 25, 2013 9:38 PM
        Please note all of the above is IdentityMinder specific, but am noticing that this problem affects other CA products, which interface with the report server as well.
        
        In case you're still having issues you may want to implement tracing on the job server to try to isolate what might be causing the issue.
        
        The job server processes the report requests, but it then spawns off a JobServerChild process to do the actual request - and it is this where I am guessing the connection is failing.
        
        There are two ways to get logging on BO, either via _trace.ini files or via the CMC (Central Management Console).
        
        a) If via CMC then
        Add the following to properties of Servers/"Crystal Report Job Server"
        -type OUTPROC -trace xs -reqtrace on -sdktrace on -crpetrace 7
        
        b) If via trace.ini files taking template from \SC\CommonReporting3\BusinessObjects Enterprise 12.0\logging\logConfig\BOtrace.ini
        
        create JobServerClient_trace.ini
        Give setting something similar to :
        
        active = true;
        importance = xs;
        alert = true;
        severity = ' ';
        keep = false;
        size = 100 * 1000;
        
        Then place JobServerChild_trace.ini in directory :
        \SC\CommonReporting3\BusinessObjects Enterprise 12.0\win32_x86
        
        Restart the JobServer service.
        
        For a comprehensive guide on BusinessObjects 3.1 debug and tracing, please see this document.
        Like • Show 1 Like1
        Actions
        Chris_Thomas @ Chris_Thomas on Oct 22, 2014 7:17 PM
        If you receive registry key not found, please make sure you're running the simplify.vbs from a cmd shell, which was "Run As Administrator"
        Like • Show 1 Like1
        Actions
Ashok kumar Muthu Mar 24, 2016 2:04 PM
This is really a helpful tip, I had similar issue on the Linux server fixed it for me. Below are the steps for Linux servers:
Integrated Documents
Like • Show 0 Likes0
Actions

Run this on your IdM Report Server if you Can't view reports.

Outcomes

Attachments

Attachments

Related Content

Recommended Content

Incoming Links