ReportServer Forum

kirti

Member

Registered: 2017-01-13

Dynamic Report Issue

Hi
I am not able to preview or export dynamic report it is giving Internal Server Error.Whereas same query is getting executed in MSSQL in 2 to 3 minutes

Thanks

Last edited by kirti (2018-01-25 06:09:20)

jalbrecht

Administrator

Registered: 2016-10-21

Re: Dynamic Report Issue

Hi kirti,
can you be a little more precise ?
wbr jan

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

Hi jalbrecht,
Actually i had created one query which is fetching records from 3 different table using union all.For e.g
Select * from table 1
union all
Select * from table 2
union all
Select * from table 3
This query gives internal server error while previewing or exporting. Whereas it is getting executed in MSSQL in 2 to 3 minutes.

But when i execute it block by block one at a time in Dynamic report like
In first Select * from table 1 
Secondly Select * from table 2
And Thirdly Select * from table 3
It gives proper output i.e preview as well as export

Thanks in advance

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi kirti,

1. what happens if you try to create a dynamic list and put only this query:

Select * from table 1
union all
Select * from table 2

and then press the button "execute".  Do you get the error?

2. If you put the query:
Select * from table 1
union all
Select * from table 2
union all
Select * from table 3

and then press "execute": do you get the error ? If yes, could you check your tomcat logs for the error ?

I think this may be a timeout issue. Could you also check:

3. Do the same for a table that *does not* take 2 or 3 minutes to execute. For example you can  execute:
Select top(10) * from table 1
union all
Select top(10) * from table 2
union all
Select top(10) * from table 3

Are you getting the error when you press "execute"?

Regards,
Eduardo

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

Hi eduild
Yes i had tried this
Select top(10) * from table 1
union all
Select top(10) * from table 2
union all
Select top(10) * from table 3
and it still gives error

I would try this
Select * from table 1
union all
Select * from table 2

And where do i find error logs so that i could paste error log for your better understanding

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi kirti,

please answer all 3 questions.

Are you getting the error when you press the "execute" button ?

The logs can be found in your tomcat installation in the "logs" directory. Please post only the relevant logs, i.e. the logs appearing when you get the error. For making sure, backup and delete all old logs, restart tomcat, make the error appear, then post these logs.

Regards,
Eduardo

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

Hi Eduardo,

Are you getting the error when you press the "execute" button ?
No it does not give error on click of execute button.

Log File:-
Caused by: net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Query could not be executed: Socket closed
    at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:325)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:424)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:416)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:148)
    at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:118)
    at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.loadData(TableReportUtilityServiceImpl.java:321)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1748)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1732)
    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1826)
    at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:5418)
    at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:6730)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:424)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
    at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:304)

It gives error while previewing or exporting it.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi kirti,

1. do you have a http server installed too, e.g. apache tomcat ?
2. How did you install reportserver? Manual installation or bitnami ?
3. What version are you using?

Regards,
Eduardo

Paresh.Damani

Member

Registered: 2017-07-10

Re: Dynamic Report Issue

Hi Eduardo,

1. do you have a http server installed too, e.g. apache tomcat ?
    Yes
2. How did you install reportserver? Manual installation or bitnami ?
    We installed with bitnami installer then we manual update version 3.0.2 Rev. 6 as per guide provided on Report server forum
3. What version are you using?
    version 3.0.2 Rev. 6

My observation
Our report query takes approx 3 minutes to execute in SQL same query execute on report  server takes approx 9 Minutes and report throw exception. I checked in SQL profiler report server execute query 3 times 1. Page count  2. Record count 3. display report Is there any way to stop this ?
Is there any time out configuration? where we can increase  time out

executing query 3 times is biggest problem and it's impact on our report execution

Regards,
Paresh Damani

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi Paresh,

please check your server timeouts as well as your database timeouts. For example, for your Tomcat, your session timeout can be found in the web.xml file.
For your HTTP Server: Proxytimeout=600 => (default settings) in configuration file httpd.conf. Please check what is this value in your case.

Regards,
Eduardo

Paresh.Damani

Member

Registered: 2017-07-10

Re: Dynamic Report Issue

Hi Eduardo,

Session timeout = 30 in web.xml file and Proxytimeout=600 in  httpd-app.conf File

Regards,
Paresh Damani

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi Paresh,

Proxytimeout=600 are 600 secs, thus 10 minutes, which is what you observe in your timing observations.

Adjust this to correct the behavior and restart your http server.

Regards,
Eduardo

Paresh.Damani

Member

Registered: 2017-07-10

Re: Dynamic Report Issue

Hi Eduardo,

Thanks for your support. Proxytimeout set to 3000 and now report display. Is there anyway to improve or stop query execution 3 times to display single report as I mentioned in my early post query execute 3 times for  1. Record count,2. Page count 3. display final report. My report query gives result in 3 min. in SQL but same is taking almost 10 min. in report server because same query execute 3 times

Regards,
Paresh

jalbrecht

Administrator

Registered: 2016-10-21

Re: Dynamic Report Issue

Hi Paresh,

ReportServer FrontEnd is to support EndUsers performing reporting duties. Therefore the Report is executed three times to support the user with the resp. functionality. If you have a slow or heavy duty you have to either improve performance or schedule the report to run and being send out during the nigeht.

What is the specific business case of this report?

wbr jan

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

For completeness:

The 3 queries you mention are all necessary in dynamic lists. But the reasons are other as you say: Let's observe an example query

select f_name from myTable

and MSSQL as a RDBMS.

1) Query to get the column names:

SELECT TOP 0 * FROM (SELECT * FROM (select f_name from myTable
) wrappedQry) limitQry

2) Query to get the first 50 rows:

SELECT "xx__rs_col_0" FROM (SELECT *, (ROW_NUMBER() OVER(ORDER BY (SELECT '1' as a))) rowNum FROM (SELECT "xx__rs_col_0" FROM ( SELECT f_name AS "xx__rs_col_0" FROM ( select f_name from myTable
) colQry) aliasQry) mssqlOffQry1 )mssqlOffQry2 WHERE mssqlOffQry2.rowNum BETWEEN 0 AND 50

3. Query to get the row count:

SELECT COUNT(*) FROM (SELECT "xx__rs_col_0" FROM ( SELECT f_name AS "xx__rs_col_0" FROM ( select f_name from myTable
) colQry) aliasQry) countQry

These 3 queries are necessary for 1) getting the column names. Imagine your query is "select * from myTable". In order to allow the user to select the columns, this query is necessary. 2) the query 3) the count query necessary for pagination.

As jalbrecht wrote, it reportserver is designed to support end users in their reporting tasks. For heavy queries you have the possibility of scheduling the reports e.g. overnight, so the users can find the results in their teamspace or their emails.
So I have the same question as jalbrecht: what is the business case of this heavy duty report?

Regards,
Eduardo

Paresh.Damani

Member

Registered: 2017-07-10

Re: Dynamic Report Issue

Hi edulid,

Thanks for your support. Can I pass Page No. as a parameter to my query. I am thinking to handle customise pagination in my query. like your example WHERE mssqlOffQry2.rowNum BETWEEN 0 AND 50 can I use this as where or top clause in my query

Regards,
Paresh Damani

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Dynamic Report Issue

Hi Paresh,

you may put in your query whatever you need, for example:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY myId ) AS MyRowNum, *
          FROM      myTable
        ) AS RowConstrainedResult
WHERE   MyRowNum >= ${from}
    AND MyRowNum < ${until}

Again, you should optimize the query for better results, or schedule the report overnight, as it "only" takes 10 mins to execute. You didn't answer our question: what is your business case of this heavy duty report?

Regards,
Eduardo

Paresh.Damani

Member

Registered: 2017-07-10

Re: Dynamic Report Issue

Hi Eduardo,

Sorry for late replay. we optimised and change approach in our query. Business case of these reports are we have different users they require data for bigger date range e.g. 1 year for analysis.Some of our tables have huge data and query on these tables for bigger date range result in performance issue, now we change approach like Data warehouse for reports so now our reports gives result faster.

My next query can we pass date parameter as yyyymmdd format without changing user display date format

Regards,
Paresh Damani

jalbrecht

Administrator

Registered: 2016-10-21

Re: Dynamic Report Issue

Hi Paresh,

i assume you want to use the date picker on a char containing date values. This is not possible as of today. We are thinking of a date2char parameter that allows to combine the best of both worlds, i.e. use unified expressions to define a default value (for example last end of month), use the date picker for selection and pass a char value to the database to control the effect of the parameter easily.

We are working on it [RS-2889] ...

jan