How to resolve current transaction is aborted issue in the BOBJ Migration project(Netezza to PostgreSQL)

[rathish_s4]

Problem Statement :After migrating the Web Intelligence Reports from Netezza to PostgreSQL database, while refreshing/scheduling the report we are getting the current transaction is aborted issue.

This blog explains the sbo file settings for BOBJ reports migration from Netezza to PostgreSQL database. Each database drivers are different SBO files. I would like to present you the solution for current transaction is aborted, commands ignored until end of transaction block; Error while executing the query

Frequent causes are

• Reporting database drivers not loaded or found


• An error returned by the reporting database indicating a connection or other system issue


• Network connectivity issues between the BI server and the repo3`rting database


• Incorrect SQL or MDX being passed to the reporting database.


• Incorrect prompt values being passed to the reporting database.

Please ensure all the above mentioned causes are validated and confirmed ,although still you are facing the same issue , then follow the below steps (ODBC or JDBC Connections)

Step 1: Login to BO server
Step 2: Go to C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc
Step 3: Take the backup of the particular database driver .sbo file
Step 4: Add the below parameter in the .sbo file and save the file

<Parameter Name="Transaction Mode">AutoCommit</Parameter>

Step 5: Restart the BO server
Step 6: Run/Refresh/schedule the report.

Added the sample PostgreSQL SBO file content

<?xml version="1.0" encoding="UTF-8"?><DriverConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../sbo.xsd">

<Defaults>

<Parameter Name="Transaction Mode">AutoCommit</Parameter>

<Parameter Name="Family">Generic</Parameter>

<Parameter Name="SQL External File">postgresql</Parameter>

<Parameter Name="SQL Parameter File">postgresql</Parameter>

<Parameter Name="Driver Capabilities">Query</Parameter>

<Parameter Name="Description File">odbc</Parameter>

<Parameter Name="Extensions">odbc</Parameter>

<Parameter Name="CharSet Table">postgresql</Parameter>

<Parameter Name="Array Fetch Available">True</Parameter>

<Parameter Name="Array Bind Available">False</Parameter>

<Parameter Name="Empty String">EmptyString</Parameter>

<Parameter Name="Query TimeOut Available">True</Parameter>

<Parameter Name="Connection Status Available">False</Parameter>

<Parameter Name="Driver Name">PostgreSQL.*</Parameter>

</Defaults>

<DataBases> 

<DataBase Active="Yes" Name="PostgreSQL 9" Platform="MSWindows,Linux">

<Aliases>

<!-- You can add an alias here if you are using some connections that are defined with an older database engine -->

<Alias>PostgreSQL 8</Alias>

</Aliases>

<Libraries>

<Library Platform="MSWindows">dbd_wodbc3</Library>

<Library Platform="MSWindows">dbd_odbc3</Library>

<Library Platform="Unix">dbd_uxodbc3</Library>

</Libraries>

<Parameter Name="Family">PostgreSQL</Parameter>

<Parameter Name="Extensions">postgresql9,postgresql,odbc</Parameter>

</DataBase>

</DataBases>

</DriverConfiguration>

SAP Refer Note : https://userapps.support.sap.com/sap/support/knowledge/en/2054721

 

Conclusion: This blog help you to resolve the current transaction is aborted issue in the Web Intelligence Report Refreshing and Scheduling

Feedback and comments are appreciated Thanks for stopping by, and hope this helps at least a few developers/users out there.,