You are not logged in.
Hi. I'm evaluating ReportServer for my organization. I've installed the open source version and have been testing some reports. I have reports in BIRT which I've uploaded to ReportServer ok, but execution and scheduling has been a mix bag.
First, I have a small report, only about 80 rows, works fine on BIRT, works fine on ReportServer, can be scheduled to export to Excel on my teamspace no problem.
The problem is with the larger report. It's about 450K rows. It runs fine on BIRT and runs fine on ReportServer as well. However, when I try to export to Excel I could not get it to work. On BIRT, it runs fine, and I can export data to CSV, but exporting the report to Excel fails. On ReportServer, it would show that it ran successfully in scheduler, but no excel file is outputted to teamspace, and I found out that you can't export to CSV for BIRT reports on ReportServer.
Realizing this may be a problem with large size Excel export, I tried experimenting with Dynamic Lists and exporting to CSV. First thing I found was, unlike BIRT, Dynamic List doesn't support CTE. Ok fine, I rewrote the SQL without CTE. It runs ok, and I can even export it to CSV ok (it generates a 80mb file), however, when I try to schedule it, again scheduler says job executed fine, but I don't get the file in the teamspace folder. Is there some sort of limiter? I don't understand why it would works fine when I run the Dynamic List and export to CSV manually, but not through scheduler/teamspace? does anyone have any ideas why?
Offline
Hi monteillin,
"it would show that it ran successfully in scheduler, but no excel file is outputted to teamspace,"
-> this is strange. Are you sure that for small reports with exactly the same steps, these appear in the TeamSpace ? It may take longer to appear in the TeamSpace, but it should be there.. can you check this?
First thing I found was, unlike BIRT, Dynamic List doesn't support CTE.
-> Dynamic lists support CTE with a special syntax: https://reportserver.net/en/guides/admi … amic-List/ , Common Table Expressions (CTEs)
do you get any error in the tomcat logs? What version exactly are you using (rsversion.properties)
Regards,
Eduardo
Offline
Thank you for the response Eduardo. Yes I'm sure that's the case. My smaller test report was in BIRT, but just to make sure it's apples to apples, I ported the SQL as a Dynamic List. This small dynamic list ran fine, exported to CSV ok, sent to teamspace ok, and scheduled export to CSV ok.
For the larger dynamic list, I tried again today to run it manually. The report ran ok (I can see preview), it exported to CSV ok, but when I tried to send to teamspace, I get the following error:
net.datenwerke.gxtdto.client.servercommunication.exceptions.ServerCallFailedException: Error while committing the transaction<br> followed by around 100 lines of error.
This is probably why the scheduled export didn't work. The two dynamic lists follow the exact same process. The only change to the default setting is changing the delimiter from ; to ,.
BTW.. I followed the instructions on CTE page you sent me and encapsulated the subquery with /*<rs:cte>*/ and /*</rs:cte>*/ but it doesn't work unfortunately. I still get error when i try to run it.
The SQL format is essentially as follows:
with N as
( select Y.a,X.b,V.c
from X
inner join Y
inner join Z
inner join U
inner join V
where X.b in (……)
)
select I.d,N.b,N.c,I.g,L.h,M.k
from I
inner join J
inner join N
left join L
left join M
Any help you can provide will be greatly appreciated!
Offline
Hi monteillin,
"followed by around 100 lines of error."
-> these lines are very important in order to determine the cause of the error. Can you please post the complete error stack ?
Regards,
Eduardo
Offline
this what I'm able to get from the error msg
net.datenwerke.gxtdto.client.servercommunication.exceptions.ServerCallFailedException: Error while committing the transaction<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.mapException(CatchStacktraceInterceptor.java:74)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:47)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.exportIntoTeamSpace(<generated>)<br> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<br> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)<br> at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<br> at java.base/java.lang.reflect.Method.invoke(Method.java:566)<br> at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.CGLIB$processCall$7(<generated>)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0$$FastClassByGuice$$caf99de0.invoke(<generated>)<br> at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.processCall(<generated>)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.CGLIB$processCall$8(<generated>)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0$$FastClassByGuice$$caf99de0.invoke(<generated>)<br> at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.processCall(<generated>)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)<br> at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)<br> at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)<br> at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.CGLIB$service$23(<generated>)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0$$FastClassByGuice$$caf99de0.invoke(<generated>)<br> at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)<br> at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)<br> at net.datenwerke.rs.scheduleasfile.server.scheduleasfile.ScheduleAsFileRpcServiceImpl$$EnhancerByGuice$$b5e630e0.service(<generated>)<br> at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)<br> at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)<br> at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)<br> at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)<br> at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)<br> at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)<br> at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)<br> at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)<br> at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)<br> at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)<br> at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)<br> at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)<br> at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)<br> at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)<br> at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)<br> at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)<br> at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)<br> at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)<br> at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)<br> at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)<br> at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)<br> at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)<br> at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)<br> at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:431)<br> at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)<br> at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)<br> at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)<br> at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)<br> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)<br> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)<br> at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)<br> at java.base/java.lang.Thread.run(Thread.java:834)<br>Caused by: javax.persistence.RollbackException: Error while committing the transaction<br> at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:86)<br> at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:87)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<br> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)<br> at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)<br> at java.base/java.lang.reflect.Method.invoke(Method.java:566)<br> at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)<br> at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)<br> at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)<br> at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)<br> at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)<br> at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br> ... 32 more<br>Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement<br> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)<br> at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)<br> at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:67)<br> ... 52 more<br>Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement<br> at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)<br> at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)<br> at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)<br> at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2884)<br> at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3384)<br> at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)<br> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:451)<br> at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:336)<br> at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:335)<br> at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)<br> at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1224)<br> at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:464)<br> at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2905)<br> at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2281)<br> at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)<br> at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:146)<br> at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)<br> at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:230)<br> at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)<br> at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)<br> ... 52 more<br>Caused by: java.sql.BatchUpdateException: Could not send query: max_allowed_packet exceeded. stream size 62147224 is > to max_allowed_packet = 33554431<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:374)<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeUpdate(MariaDbServerPreparedStatement.java:347)<br> at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1449)<br> at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)<br> ... 70 more<br>Caused by: java.sql.SQLTransientException: Could not send query: max_allowed_packet exceeded. stream size 62147224 is > to max_allowed_packet = 33554431<br> at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:131)<br> at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQueryEpilog(MariaDbServerPreparedStatement.java:336)<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:291)<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:369)<br> ... 73 more<br>Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Could not send query: max_allowed_packet exceeded. stream size 62147224 is > to max_allowed_packet = 33554431<br> at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:585)<br> at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:279)<br> ... 74 more<br>Caused by: org.mariadb.jdbc.internal.stream.MaxAllowedPacketException: max_allowed_packet exceeded. stream size 62147224 is > to max_allowed_packet = 33554431<br> at org.mariadb.jdbc.internal.stream.PacketOutputStream.checkPacketMaxSize(PacketOutputStream.java:252)<br> at org.mariadb.jdbc.internal.stream.PacketOutputStream.internalFlush(PacketOutputStream.java:261)<br> at org.mariadb.jdbc.internal.stream.PacketOutputStream.finishPacket(PacketOutputStream.java:193)<br> at org.mariadb.jdbc.internal.packet.send.SendExecutePrepareStatementPacket.send(SendExecutePrepareStatementPacket.java:136)<br> at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:578)<br> ... 75 more<br>
Offline
Hi monteillin,
the error is:
Caused by: java.sql.SQLTransientException: Could not send query: max_allowed_packet exceeded. stream size 62147224 is > to max_allowed_packet = 33554431
which is sent by your MariaDB driver.
For solving this, please configure your DB. Here more information: https://confluence.atlassian.com/confkb … 43425.html
https://dev.mysql.com/doc/refman/5.6/en … large.html
https://stackoverflow.com/questions/137 … gexception
SOLUTIONS:-
You can see it's current value in mysql like this:SHOW VARIABLES LIKE 'max_allowed_packet'
You can try to change it like this, but it's unlikely this will work on shared hosting:
SET GLOBAL max_allowed_packet=16777216;
and restart mysql service..
This is for MySQL but should work analogously for MariaDB.
Regards,
Eduardo
Offline
Thank you very much for the guidance Eduardo. I will try it.
Offline
woohoo! it worked! Thank you Eduardo. Now I just need to figure out how to get CTE to work and we're in business.
Offline
Hi monteillin,
I am glad it worked. Can you please post (for completeness) the specific MariaDB changes you made in order for this to work ?
Regarding CTEs, please take a look here:
-> Dynamic lists support CTE with a special syntax: https://reportserver.net/en/guides/admi … amic-List/ , Common Table Expressions (CTEs)
Basically, you have to write two comments like this:
/*<rs:cte>*/
WITH USERS_CTE (username, firstname, lastname)
AS (
SELECT username, firstname, lastname from RS_USER
)
/*</rs:cte>*/
SELECT * from USERS_CTEand then it should work.
Regards,
Eduardo
Offline
I figured out the query with CTE! Using the encapsulation syntax does work! turns out the reason why the query didn't work last time is the syntax doesn't support having a semi-colon at the end.. of all things. 8)
We're going to start doing more large scale testing with this tool for our existing reports. I'm going to need to look into more advanced functions like user administration, and report parameterization.
Offline
Hi monteillin,
so are you using the syntax with /*<rs:cte>*/ or what syntax exactly is working in your case? Can you please post an example?
Regards,
Eduardo
Offline
yes I am using the /*<rs:cte>*/ encapsulation on the subquery. I've sent you the query in the other thread. Thanks for all your help Eduardo!
Offline
As to your other question, there was a mysql configuration file my.cnf where we increased the packet size from the default 32M to 256M
Offline