Calling SP with temporary table in jasper report

[hari.kumar.s]

Hi folks

i am using SQL Server 2008.

i m trynig to call a stored procedure using Jaspersoft Studio. I am using sqljdbc4.jar.

the procedure has a temporary table. I cannot read fields in the report.

I am getting an error

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ReportName
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:468)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:443)
    at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:335)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ReportName
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1114)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1314)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:931)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1716)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)
    ... 6 more

 

Thanks

Hari

[Ravi Kumar]

Please see the post  

http://community.jaspersoft.com/questions/874046/problem-design-report-using-batch-processed-query

 

[zh3ntil]

You should disable the rows() affected message by running SET NOCOUNT ON on the SQL server. To do that add SET NOCOUNT ON to your SP. I have solved this error with this before for the same case.

Calling Microsoft SQL Stored Procedures from iReport

http://community.jaspersoft.com/wiki/calling-microsoft-sql-stored-procedures-ireport