jpasquier Posted November 23, 2006 Share Posted November 23, 2006 Is there anyone that has succesfully deployed JasperIntelligence with a jasperserver database on Ms SQL Server ? Is there any special requirements ? Thank you Jonathan Link to comment Share on other sites More sharing options...
rosisr Posted November 27, 2006 Share Posted November 27, 2006 Hi, you just have to add the right driver into lib directory. I've used jtds-driver, and I put the .jar file into /lib directory and it works for me. Good luck!! Link to comment Share on other sites More sharing options...
jpasquier Posted November 27, 2006 Author Share Posted November 27, 2006 I tried with the jTDS 1.2 driver and the jasperserver deployment failed on JBoss.I copy a part of the log :2006-11-27 17:04:15,687 ERROR [org.springframework.web.context.ContextLoader] Context initialization failedorg.springframework.beans.factory.BeanCreationException: Error creating bean with name 'quartzScheduler' defined in ServletContext resource [/WEB-INF/applicationContext-report-scheduling.xml]: Invocation of init method failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.org.quartz.SchedulerConfigException: Failure occured during job recovery. [see nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. [see nested exception: java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]] at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:493) at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:144) at org.springframework.scheduling.quartz.LocalDataSourceJobStore.initialize(LocalDataSourceJobStore.java:133) at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:1010) at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1152) at org.springframework.scheduling.quartz.SchedulerFactoryBean.createScheduler(SchedulerFactoryBean.java:645) at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:546) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:860) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:829) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:409) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:238) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:148) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:247) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:331) at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:155) at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:240) at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:178) at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3669) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4104) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.apache.commons.modeler.BaseModelMBean.invoke(BaseModelMBean.java:503) at org.jboss.mx.server.RawDynamicInvoker.invoke(RawDynamicInvoker.java:150) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.apache.catalina.core.StandardContext.init(StandardContext.java:5005) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.apache.commons.modeler.BaseModelMBean.invoke(BaseModelMBean.java:503) at org.jboss.mx.server.RawDynamicInvoker.invoke(RawDynamicInvoker.java:150) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.web.tomcat.tc5.TomcatDeployer.performDeployInternal(TomcatDeployer.java:274) at org.jboss.web.tomcat.tc5.TomcatDeployer.performDeploy(TomcatDeployer.java:91) at org.jboss.web.AbstractWebDeployer.start(AbstractWebDeployer.java:357) at org.jboss.web.WebModule.startModule(WebModule.java:68) at org.jboss.web.WebModule.startService(WebModule.java:46) at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:272) at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:222) at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:897) at $Proxy0.start(Unknown Source) at org.jboss.system.ServiceController.start(ServiceController.java:418) at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177) at $Proxy28.start(Unknown Source) at org.jboss.web.AbstractWebContainer.start(AbstractWebContainer.java:394) at org.jboss.deployment.MainDeployer.start(MainDeployer.java:964) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:775) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:738) at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:121) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:127) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177) at $Proxy8.deploy(Unknown Source) at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:325) at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:501) at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:204) at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:277) at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:272) at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:222) at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:897) at $Proxy0.start(Unknown Source) at org.jboss.system.ServiceController.start(ServiceController.java:418) at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177) at $Proxy4.start(Unknown Source) at org.jboss.deployment.SARDeployer.start(SARDeployer.java:273) at org.jboss.deployment.MainDeployer.start(MainDeployer.java:964) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:775) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:738) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:722) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:121) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:127) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177) at $Proxy5.deploy(Unknown Source) at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:434) at org.jboss.system.server.ServerImpl.start(ServerImpl.java:315) at org.jboss.Main.boot(Main.java:195) at org.jboss.Main$1.run(Main.java:463) at java.lang.Thread.run(Thread.java:595) And my sqlserver-ds.xml file :<local-tx-datasource> <jndi-name>jdbc/jserver</jndi-name> <connection-url>jdbc:jtds:sqlserver://localhost:1433/jasperserver;SelectMethod=cursor</connection-url> <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class> <user-name>login</user-name> <password>pwd</password> <min-pool-size>5</min-pool-size> <max-pool-size>20</max-pool-size> <!-- sql to call on an existing pooled connection when it is obtained from pool --> <check-valid-connection-sql>SELECT 1</check-valid-connection-sql> <metadata> <type-mapping>MS SQLSERVER2000</type-mapping> </metadata> </local-tx-datasource> Any suggestions ? Thank you. Jonathan Link to comment Share on other sites More sharing options...
swood Posted November 28, 2006 Share Posted November 28, 2006 The advice rosisr gave you was about connecting to MSSQL as a data source for a report. What you are after is MSSQL as the metadata repository. Have a look at the developer documentation at http://jasperforge.org/sf/docman/do/listDocuments/projects.jasperintelligence/docman.root to see how you can create a MSSQL database script and deploy it. There are forum threads and patches about the Postgres and Oracle implementations which could help you. ShermanJasperSoft Link to comment Share on other sites More sharing options...
jpasquier Posted December 6, 2006 Author Share Posted December 6, 2006 I solved the problem by : 1- Using the new sqljdbc.jar (Ms SQLServer JDBC driver 1.1 supplied by Microsoft), the new version fixes some bugs. 2- Adding a line in js.quartz.properties file in order to change the selectWithLockSQL property that raises an error during the JI deployment on JBoss : org.quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ? 3- Deleting the unique constraint on the JIRessource.childrenFolder column in JI database in order to allow the creation of new report units.Post edited by: jpasquier, at: 2006/12/06 13:23 Link to comment Share on other sites More sharing options...
lucianc Posted December 6, 2006 Share Posted December 6, 2006 Regarding #3: It seems that, unlike MySQL/PostgreSQL/Oracle/etc, MS SQL doesn't allow multiple NULL values for a nullable unique column, i.e. it considers two NULL values equal. This is why the unique constraint doesn't work in MS SQL server. We will remove the JIResource.childrenFolder unique constraint since it's not actually used for anything, it's only there to reflect that the column is logically unique. Regards,Lucian Link to comment Share on other sites More sharing options...
bklawans Posted December 6, 2006 Share Posted December 6, 2006 It seems that, unlike MySQL/PostgreSQL/Oracle/etc, MS SQL doesn't allow multiple NULL values for a nullable unique column, i.e. it considers two NULL values equal. This is why the unique constraint doesn't work in MS SQL server. Too bad - that is just plain wrong. The SQL spec is pretty clear that NULLs do not equal anything else, not even each other. It makes one exception - in a group by you are supposed to combine all the NULLs into a single group. Old versions of SQL Server worked like the other databases and allowed multiple NULLs. -Barry Link to comment Share on other sites More sharing options...
vannghia184 Posted December 16, 2009 Share Posted December 16, 2009 Hi JonathanYou should add SelectMethod=cursor into your connection string. jdbc:sqlserver://$host$:$port$;databaseName=jasperserver;SelectMethod=cursorjs.jdbc.propertiesjs-sqlserver-ds.xmlPost Edited by vannghia184 at 12/16/2009 08:11 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now