sql-serverjasperserver

A Microsoft SQL Server stored procedure operates successfully within Jasper Studio, but encounters failures when executed on the jasper server

I have installed Jasper Report Server 8.2 and Jasper Report Studio 6.2 community editions to generate a report that retrieves data from a SQL Server stored procedure. Below is the stored procedure:

exec BaqyatMajorByYear '1400', '1400', '1399'

I have successfully established a connection between Jasper Studio and SQL Server, and I can confirm that the data from the stored procedure is previewing correctly within Jasper Studio.

(https://i.sstatic.net/32ayT.png)

The Jasper Server is also connected to a database, and normal select query reports within the server are functioning as expected. But, when attempting to access a report generated from a stored procedure, an error occurs, and the server displays the following error message:

There was an error on the server. Try again or contact site administrators. (Error UID: ab76308d-e030-4443-b133-b2e34bdd2fd8) com.jaspersoft.jasperserver.api.ErrorDescriptorException: There was an error on the server. Try again or contact site administrators. (Error UID: ddd4e95a-9328-42a4-9bf6-cdf171e3c672) at com.jaspersoft.jasperserver.remote.services.ReportExecution.getFinalReportUnitResult(ReportExecution.java:173) at com.jaspersoft.jasperserver.remote.services.impl.RunReportServiceImpl$2.run(RunReportServiceImpl.java:538) at com.jaspersoft.jasperserver.api.logging.util.LoggableExecutorService$1.run(LoggableExecutorService.java:84) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

I have attempted various resources, but regrettably, none of them yielded the desired outcome.

  1. https://community.jaspersoft.com/wiki/how-execute-ms-sql-stored-procedure-tibco-jasperreports-server
  2. https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=782366
  3. https://ralessi.wordpress.com/2018/10/30/sql-server-stored-procedure-in-jasper-reports/

I would like to inquire whether Jasper Server has the capability to execute SQL Server stored procedures. If this is indeed possible, I would appreciate guidance on how to achieve this functionality.

Solution

  • For calling SQL Server stored procedures only these two steps worked for me!

    1. in jasper studio you should select plsql as language by right clicking on Your Report Name from Outline tab

    2. your query should have its stored procedure's parameters if it have some like this

      exec yourProcedure @firstParam= value , @SecondParam= value

    then publish it to jasperReport server and run it