14 Replies Latest reply on Mar 25, 2009 11:23 AM by treespace

    Microsoft JDBC Driver for SQL Server 2008 Failure

    treespace Novice

      I'm using Java 1.6 with JBoss AS 5.1.0 (beta 1) and SQL Server 2008.

      I downloaded the driver for SQL Server from microsoft:

      http://msdn.microsoft.com/en-us/data/aa937724.aspx

      JBoss claims my mssql-ds.xml driver "appears to be wrong" with this URL prefix:

      jdbc:microsoft.sqlserver://localhost: ...

      Googled up a reference to somebody using this URL instead:

      jdbc:sqlserver://localhost: ...

      That sort of works, sort of.

      The driver itself complains about Java 1.6 and says to use the Type 4 driver (they give you both in the download). Type 4 connects just fine.

        • 1. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
          treespace Novice

          Another Failure Point: SQL Server fails as DefaultDS because CLUSTERED is a reserved word. If HSQL truly cannot be used in production, then the use of reserved words has to be addressed.

          • 2. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
            treespace Novice

            Oops! Just read "this is not a database forum" sticky message. Can I move or duplicate post?

            • 3. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
              Peter Johnson Master

              What all did you do to change DefaultDS to use SQL Server instead of HSQL? Simply replacing hsqldb-ds.xml is not sufficient because for various services, such as messaging, there are database-specific configuration files that must be deployed. Such a configuration file for SLQ Server would not use a reserved word.

              Also, it is easier to help you if you post the exception stack traces.

              • 4. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                treespace Novice

                The only change was to use DefaultDS as the jndi-name in mssql-ds.xml and remove hsqldb-ds.xml to eliminate the conflict. The assumption being the file name is irrelevant (any *-ds.xml pattern will do) and that all JDBC drivers must adhere to precisely the same interface.

                Looks like Quartz is using a column called CLUSTERED in a query? That raises some interesting questions in terms of reserved words and how a vendor can add extra ones not addressed by JDBC (or some other spec).

                Here is the error message:

                17:59:30,625 INFO [QuartzScheduler] Quartz Scheduler v.1.5.2 created.
                17:59:30,640 INFO [RAMJobStore] RAMJobStore initialized.
                17:59:30,640 INFO [StdSchedulerFactory] Quartz scheduler 'DefaultQuartzScheduler' initialized from default resource file in Qua
                rtz package: 'quartz.properties'
                17:59:30,640 INFO [StdSchedulerFactory] Quartz scheduler version: 1.5.2
                17:59:30,640 INFO [QuartzScheduler] Scheduler DefaultQuartzScheduler_$_NON_CLUSTERED started.
                17:59:31,140 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS
                ' to JNDI name 'java:DefaultDS'
                17:59:34,671 INFO [ServerPeer] JBoss Messaging 1.4.3.GA server [0] started
                17:59:38,343 WARN [JDBCSupport] SQLException caught, SQLState S0001 code:156- assuming deadlock detected, try:1
                com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'CLUSTERED'.
                at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

                • 5. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                  Peter Johnson Master

                  Sounds like all you did was replace hsqldb-sd.xml with mssql-ds.xml, which, as I pointed out, is not sufficient to change the DefaultDS data source to use a different database. You also have to change the SQL statements used my Quartz and by the messaging service. The messaging ones should be fairly easy - check the docs/examples/jms directory for a SQL Server-specific config file. Quartz is a little tougher - you will have ot go to the Quartz web site and download the Quartz sources, find the file containing the SQL Server SQL statements, and then edit JBoss' Quartz config file, replacing the existing SQL statements.

                  • 6. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                    Peter Johnson Master

                    I juts now lokked at the docs/examples/jms directory in 5.1.0-beta1. The file you need is not there. Looks like you will have to download JBoss Messaging and get the persistence config files from there.

                    • 7. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                      Peter Johnson Master

                      I looked at messaging in AS 5.1.0 - it is version 1.4.3.GA. But there is no such version on the JBoss Messaging download page, so it would appear that version 1.4.3.GA is a AS 5.1.0 Beta-specific version only. However, I noticed that there is no difference between the hsqldb-persistence-service.xml file in AS 5.0.1.Beta1 and in AS 5.0.0.GA. Therefore, you should be able to use the docs/examples/jms/mssql-persistence-service.xml from AS 5.0.0.GA in AS 5.1.0.Beta1.

                      Also, please post the full stack trace for the exception - I want to see which Quartz class is making the database call (I am still tracking down the Quartz SQL statements - they ain't where they were in 5.0.0.GA).

                      • 8. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                        Peter Johnson Master

                        OK, I was focusing too much on the work Quartz in the log entries you posted. There are no quartz-related tables in the database. The database entry in question is for one of the messaging tables. So the information I provided in my earlier response for replacing the messaging persistence file should work for you.

                        • 9. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                          treespace Novice

                          I guess this means there's a disconnect between the expectations created by the server documentation and reality. HSQL is described as convenience not suitable for production. The prescribed solution for production is to point your DefaultDS at a production quality database.

                          The problem is customer-oblivious vendors (read: Microsoft) introducing common words to their reserved list. This is easily prevented by choosing some variation that is uncommon.

                          The next logical place to fix the problem Microsoft introduced here, is not on my machine (or the many others who might be tasked with using SQL Server) but at the source of the conflict.

                          I take it one complete "try" (there's a bunch) of the stack trace will suffice. Here it is.

                          15:11:55,909 INFO [ServerPeer] JBoss Messaging 1.4.3.GA server [0] started
                          15:11:56,080 WARN [JDBCSupport] SQLException caught, SQLState S0001 code:156- assuming deadlock detected, try:1
                          com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'CLUSTERED'.
                          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
                          at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1458)
                          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:38
                          8)
                          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338
                          )
                          at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4016)
                          at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1414)
                          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:176)
                          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:151)
                          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
                          at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
                          at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice$1LoadBindings.doTransaction(MessagingPostOffice.java:249
                          7)
                          at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice$1LoadBindings.doTransaction(MessagingPostOffice.java:248
                          0)
                          at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner.execute(JDBCSupport.java:394)
                          at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner.executeWithRetry(JDBCSupport.java:416)
                          at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.getBindingsFromStorage(MessagingPostOffice.java:2548)
                          at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.start(MessagingPostOffice.java:363)
                          at org.jboss.messaging.core.jmx.MessagingPostOfficeService.startService(MessagingPostOfficeService.java:462)
                          at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:376)
                          at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:269)
                          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:597)
                          at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:157)
                          at org.jboss.mx.server.Invocation.dispatch(Invocation.java:96)
                          at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:138)
                          at org.jboss.mx.server.Invocation.invoke(Invocation.java:90)
                          at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:140)
                          at org.jboss.mx.server.Invocation.invoke(Invocation.java:90)
                          at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
                          at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:668)
                          at org.jboss.system.microcontainer.ServiceProxy.invoke(ServiceProxy.java:206)
                          at $Proxy36.start(Unknown Source)
                          at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:42)
                          at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:37)
                          at org.jboss.dependency.plugins.action.SimpleControllerContextAction.simpleInstallAction(SimpleControllerContextAction.j
                          ava:62)
                          at org.jboss.dependency.plugins.action.AccessControllerContextAction.install(AccessControllerContextAction.java:71)
                          at org.jboss.dependency.plugins.AbstractControllerContextActions.install(AbstractControllerContextActions.java:51)
                          at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
                          at org.jboss.system.microcontainer.ServiceControllerContext.install(ServiceControllerContext.java:286)
                          at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1598)
                          at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1062)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
                          at org.jboss.system.ServiceController.doChange(ServiceController.java:688)
                          at org.jboss.system.ServiceController.start(ServiceController.java:460)
                          at org.jboss.system.deployers.ServiceDeployer.start(ServiceDeployer.java:163)
                          at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:99)
                          at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:46)
                          at org.jboss.deployers.spi.deployer.helpers.AbstractSimpleRealDeployer.internalDeploy(AbstractSimpleRealDeployer.java:62
                          )
                          at org.jboss.deployers.spi.deployer.helpers.AbstractRealDeployer.deploy(AbstractRealDeployer.java:50)
                          at org.jboss.deployers.plugins.deployers.DeployerWrapper.deploy(DeployerWrapper.java:171)
                          at org.jboss.deployers.plugins.deployers.DeployersImpl.doDeploy(DeployersImpl.java:1439)
                          at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1157)
                          at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1178)
                          at org.jboss.deployers.plugins.deployers.DeployersImpl.install(DeployersImpl.java:1098)
                          at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
                          at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1598)
                          at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1062)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
                          at org.jboss.deployers.plugins.deployers.DeployersImpl.process(DeployersImpl.java:781)
                          at org.jboss.deployers.plugins.main.MainDeployerImpl.process(MainDeployerImpl.java:698)
                          at org.jboss.system.server.profileservice.repository.MainDeployerAdapter.process(MainDeployerAdapter.java:117)
                          at org.jboss.system.server.profileservice.repository.ProfileDeployAction.install(ProfileDeployAction.java:59)
                          at org.jboss.system.server.profileservice.repository.AbstractProfileAction.install(AbstractProfileAction.java:53)
                          at org.jboss.system.server.profileservice.repository.AbstractProfileService.install(AbstractProfileService.java:355)
                          at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
                          at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1598)
                          at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1062)
                          at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
                          at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
                          at org.jboss.system.server.profileservice.repository.AbstractProfileService.activateProfile(AbstractProfileService.java:
                          310)
                          at org.jboss.system.server.profileservice.ProfileServiceBootstrap.start(ProfileServiceBootstrap.java:241)
                          at org.jboss.bootstrap.AbstractServerImpl.start(AbstractServerImpl.java:461)
                          at org.jboss.Main.boot(Main.java:216)
                          at org.jboss.Main$1.run(Main.java:546)
                          at java.lang.Thread.run(Thread.java:619)
                          15:11:56,190 WARN [JDBCSupport] Trying again after a pause

                          • 10. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                            Peter Johnson Master

                            Wow, no word whatsoever as to if you followed my advice and copied over the mssql-persistence-service.xml file...

                            • 11. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                              treespace Novice

                              Received a response from Microsoft on the need for sqljdbc4.jar.

                              "...The Microsoft SQL Server JDBC Driver 2.0 supports the JDBC 4 API that is part of the Java SE 6 platform. To use the JDBC 4 API, you must use the sqljdbc4.jar, which only works with Java SE 6 (1.6). If you only need the JDBC 3 API, you should use the sqljdbc.jar, which only works with Java SE 5 (1.5)..."



                              • 12. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                                treespace Novice

                                 

                                "PeterJ" wrote:
                                Wow, no word whatsoever as to if you followed my advice and copied over the mssql-persistence-service.xml file...


                                There were a couple of issues with that.

                                1. No such file in my 5.1.0.Beta1 installation.
                                2. Keeping DefaultDS with HSQL and adding another datasource works fine.

                                I did comment indirectly on tinkering with mssql-persistence.xml. Namely, whatever needs changing (perhaps there's another layer to the onion after mssql-persistence-service.xml) should be part of the distribution or part of the documentation.

                                Of course, if you're asking me to help resolve this on my installation of SQL Server, so the docs can be updated, I would be more than happy to. Let me know what the deal is with the missing mssql-persistence-service.xml file and we can go from there.


                                • 13. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                                  Peter Johnson Master

                                  1. Here is what I wrote:

                                  I just now looked at the docs/examples/jms directory in 5.1.0-beta1. The file you need is not there. Looks like you will have to download JBoss Messaging and get the persistence config files from there.


                                  after which I wrote:

                                  I looked at messaging in AS 5.1.0 - it is version 1.4.3.GA. But there is no such version on the JBoss Messaging download page, so it would appear that version 1.4.3.GA is a AS 5.1.0 Beta-specific version only. However, I noticed that there is no difference between the hsqldb-persistence-service.xml file in AS 5.0.1.Beta1 and in AS 5.0.0.GA. Therefore, you should be able to use the docs/examples/jms/mssql-persistence-service.xml from AS 5.0.0.GA in AS 5.1.0.Beta1.


                                  Those steps worked for me with both MySQL and PostgreSQL. They should work for SQL Server as well (I am still debating if I really want to install SQL Server, perhaps I'll place it in one of my Windows VMs, maybe Server 2008, but I refuse to install it on my main machine.)

                                  2. Of course that works fine - hsqldb is still being used for the messaging service. (I fail to understand why this is an "issue".)

                                  You also have to realize that you are using a beta - the missing messaging configuration files will show up in time for GA, and hopefully in time for CR1. For AS 5.0.0, those files didn't show up until CR1 - before them you had to get them from the JBoss Messaging download, and you had to make sure you used the exact same version of JBoss Messaging (ask me how many times I ran into problems because I failed to do this). If you dislike having to do that kind of work, you should stick to the released versions.

                                  • 14. Re: Microsoft JDBC Driver for SQL Server 2008 Failure
                                    treespace Novice

                                    >> I fail to understand why [use of HSQL] is an issue.

                                    Supposedly it's not to be used in production.

                                    >> You also have to realize that you are using a beta...

                                    I'm just pointing out an issue in replacing HSQL as the DefaultDS. It's not an issue when you leave it alone and simply add your own data sources.

                                    >> If you dislike having to do that kind of work you should stick to the released versions.

                                    It's not a like/dislike issue, the problem is you said right out off the bat that replacing DefaultDS wasn't sufficient. I'm saying, therefore, it has to be part of the documentation, which it isn't. Now you're saying this is a wrinkle in the beta release. Assuming that's true, we're good to go. We'll leave HSQL as-is, then address the production quality of HSQL downstream.