search cancel

Error to generate Service Availability report on JasperServer

book

Article ID: 200754

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

When I try extract a "Service availability report by Service name" on JasperReports, receive the following error message:

Error Message
There was an error on the server. Try again or contact site administrators. (Error UID: 9e6a6223-c5f4-43d3-8061-11ed180e6e5f)

I try to extract other reports and I have no problems.

This starts after removing the old SAP CABI integration

JASPER SERVER 6.4.3

Spectrum 10.4.0

Cause

Error message in the JasperReports:

2020-10-02 12:08:09,329 ERROR AsyncJasperPrintAccessor,pool-6-thread-3:321 - Error during report execution
net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
 at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:874)
 at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:537)
 at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
 at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1067)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1026)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:287)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
 at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:945)
 at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:896)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1812)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1143)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1072)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:967)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
 at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
 at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
 at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:440)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
 at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
 at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
 at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
 ... 3 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON sm_monitoroutages.monOutageID=sm_monitormaps.monOutageID and v_security_strin' at line 51
Query is:
SELECT 
sm_monitoroutages.serviceHealth, 
sm_monitoroutages.outageType, 
sm_monitors.monName, 
sm_monitoroutages.endTime, 
sm_monitoroutages.startTime, 
sm_monitors.monitorMH, 
sm_monitoroutages.monOutageID, 
sm_monitors.monDestroyTime, 
sm_monitors.monCreateTime, 
sm_monitors.monitorType, 
sm_slmowns.userMH, 
sm_usersmhs.userName, 
sm_users.userName, 
sm_monitors.monDescription, 
sm_monitoroutages.notes, 
sm_monitoroutages.userName, 
sm_monitoroutages.origServiceHealth
,CASE  
WHEN sm_monitoroutages.startTime<? THEN ?
ELSE
sm_monitoroutages.startTime
END  as OutageStartTimeAdjusted,
CASE  
WHEN sm_monitoroutages.endTime is null THEN ?
WHEN TIMESTAMPDIFF(SECOND,?,sm_monitoroutages.endTime)>0 THEN ?
ELSE
sm_monitoroutages.endTime
END  as OutageEndTimeAdjusted, 
CASE  
WHEN  TIMESTAMPDIFF(SECOND,?,sm_monitors.monCreateTime)<0 THEN ?
ELSE
sm_monitors.monCreateTime
END  as ModelReportStartTime,
CASE  
WHEN sm_monitoroutages.endTime is null THEN ?
WHEN TIMESTAMPDIFF(SECOND,sm_monitors.monDestroyTime,?)>0 THEN
sm_m
 at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125)
 at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
 at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:259)
 at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:287)
 at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:444)
 at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeQuery(MariaDbClientPreparedStatement.java:119)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
 ... 13 more

2020-10-02 12:08:09,365 ERROR ErrorPageHandlerAction,http-nio-8080-exec-7:118 - Error UID 3bcc3322-c49d-4b88-b37a-5328f8c0692d
net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
 at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:874)
 at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:537)
 at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
 at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1067)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1026)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:287)
 at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
 at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:945)
 at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:896)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1812)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1143)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1072)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:967)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.lang.Thread.run(Unknown Source)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
 at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
 at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
 at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
 at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:440)
 at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
 at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
 at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
 at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
 at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
 ... 3 more

Environment

Release : 

Component : JasperReports

Resolution

1. Removing the spectrum organization from Jasper.

2. Stopped Jasper tomcat. Cleared the Jasper_HOME/apache-tomcat/work directory

3. Started the Jasper tomcat

4. Go to OneClick webpage, Report Manager->Jasper Integration, and Save. then redeploy the reports.

5. Uncheck the Report Manager->Preferences Enable Security.

Attachments