Jasperserver and Ireport - JRJdbcQueryExecuter Err

[vsoneta]

Hello,

 

I have created a jrxml which is very simple, takes one input parameter and passes the information to the SQL 2000 select query which calls 2005 sql database and displays the values.

In Ireport when I click Preview it executes perfectly fine and my results gets displayed. But when I create the Report unit with the above jrxml and when I run this  it gives me an error:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : WebIntranetStoreOrdersReport at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)

 

I make it sure my input has data to be displayed.  This is happening only in Jasperserver. I have attached the jrxml

Please help. 

[vsoneta]

Here is the sql 2000  query to call SQl 2005 sql server's store proc. Mind this is working fine when I execute the sql is sql 2000 server and also in ireport, it's just not in Jasperserver ?

 

USE [Reporting]
GO
/****** Object:  StoredProcedure [dbo].[reports_CurrentStoreOrders]    Script Date: 10/23/2009 10:55:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








ALTER PROCEDURE [dbo].[reports_CurrentStoreOrders]
   @StoreNo as varchar(10)
AS

BEGIN
    SET NOCOUNT ON;


            DECLARE @sql varchar(8000)   


            SET @sql = '
 SELECT  OrderNumber, Line,OrderDate, PickUpDate, ERPDescription,QtyOrdered,ActualPrice,Tax,Department,StoreName,BTFirstName,BTLastName
 FROM OPENQUERY([hq1svrsql007\website], ''EXEC [hq1svrsql007\website].InSite.dbo.[reporting_StoreOrders] ' +
 '''''' + convert(varchar(10),@StoreNo) + ''''''')

'

 EXECUTE (@sql)

END








 

[vsoneta]

Do I need extra jar or anything do to do with OPENQUery.. Please help me...

[vsoneta]

I digged more into the error and found that it also give this error : The statement did not return a result set. Please help

[vsoneta]

I got the reason why it's giving me an error as it's not able to execute the @sql so the results set is empty and it gives an error. But how to fix it so that it executes this storeproc.

 

The issue is with the statement;

SET @sql = '
 SELECT  OrderNumber, Line,OrderDate, PickUpDate, ERPDescription,QtyOrdered,ActualPrice,Tax,Department,StoreName,BTFirstName,BTLastName
 FROM OPENQUERY([hq1svrsql007\website], ''EXEC [hq1svrsql007\website].InSite.dbo.[reporting_StoreOrders] ' +
 '''''' + convert(varchar(10),@StoreNo) + ''''''')

'
 EXECUTE (@sql)

 

Jasperserver is not executing the above sql statement -- basically query for linked server store proc... PLEASE HELP... and it works fine with the ireport PLEASE HELP I AM LIKE STUCK FROM LAST WEEK.. PLEASE

[swood]

In general, stored procedures need a different JasperReports data source/query executer that normal SQL, because of the different JDBC API being used. We have an example on the Forge for Oracle stored procedures.

 

The SET statement you show is in the stored procedure, right? Does it run outside of the report?

 

Does the stored procedure work in a report running in iReport? What sort of data source are you using in iReport for this?

 

How have you set up the data source in JasperServer? Do you have other reports that use the data source in JasperServer that do not use a stored procedure?

 

Sherman