2 Replies Latest reply on May 21, 2003 1:14 PM by Daniel Lehmann

    JBOSS 3.2.1 MSSQL Datasource problem

    trainjet Newbie

      Hi,

      I am trying to configure Jboss to use Ms SQL. I have copied the jars to the deploy folder. I also have mssql-ds.xml modified as

      ---------------------------mssql-ds.xml--------------

      <local-tx-datasource>
      <jndi-name>MSSQLDS</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://myServerName:1433;DatabaseName=Test;User=sa;Password=aaa;SelectMethod=Cursor</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      Test
      <user-name>sa</user-name>
      aaa
      <min-pool-size>100</min-pool-size>
      <max-pool-size>1000</max-pool-size>
      <blocking-timeout-millis>5000</blocking-timeout-millis>
      </local-tx-datasource>

      -----------------end of mssql-ds.xml --------------

      When I tried to access it through a session bean, I got the following error message,

      -----------------err message --------------------------


      Caugth exception: sql problem Could not create connection; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The requested instance is either invalid or not running.); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The requested instance is either invalid or not running.))

      -----------------------end of error message -------

      Questions,

      1. How do I know if port 1433 is the right port?
      2. Do I have to make an ODBC data source and somehow use it (JDBC-ODBC)?
      3. Any hint on how I can figure out the problem?

      Any help is appreciated.

      Thanks in advance.


        • 1. Re: JBOSS 3.2.1 MSSQL Datasource problem
          Daniel Lehmann Newbie

          Hi Qian,

          Unfortunately, I am away from home this week and do not have access to my actual code, but I will try to answer as best as I can from memory. If it doesn't work, let me know and I will try to answer once I'm back (June 2, 2003).

          Ok, here goes:


          1. MS SQL JDBC Problem

          a. Copy the three jars you get after installing the MS SQL JDBC driver to the server/default/lib directory (assuming you are using the default configuration, which you are if you are running JBoss from the batch/shell 'run' file in /bin).
          It seems you put them in the /deploy directory - this doesn't work.

          b. Restart JBoss ( to make sure the driver is available)

          c. Write your mssql-ds.xml and copy into /deploy
          If changing the driver from /deploy to /lib doesn't fix the problem, the only thing I can say fom the top of my head that's different with your xml compared to mine is the <connection-url>. I did not specifiy database name, user, pw etc. in there since it is given in the specific tags underneath. So, try
          <connection-url>jdbc:microsoft:sqlserver://myServerName:1433</connection-url>
          instead.

          That's really it. To answer your questions:

          1. How do I know if port 1433 is the right port?
          I believe this is default. I did not touch my DB server and it worked fine.

          2. Do I have to make an ODBC data source and somehow use it (JDBC-ODBC)?
          NOPE, just see above. The JDBC driver from Microsoft is complete in itself and does not require ODBC.

          3. Any hint on how I can figure out the problem?
          Hopefully, that's already done ;)



          2. Auto-Increment

          1. Go to the conf/standardjbosscmp-jdbc.xml. Under the MS SQL Version you are using you have to add the following tag: <auto-increment-template>dummy_entry</auto-increment-template>

          Look under the MySQL configuration in the same xml to see the example. What you actually write between the tags does not seem to matter in the case of MS SQL, but you cannot leave it empty.

          2. Now add the following in jbosscmp-jdbc.xml


          <cmp-field>
          <!--this is the primary key field-->
          ...
          <auto-increment>
          </cmp-field>

          ...

          <entity-command name="dummy-name" class="package1.subpackage.etc.X.class"/>




          The name you give the entity-command doesn't matter, but you need to specifiy the class where I put an 'X'. Now, since I do not have access to my files, you'll have to find it yourself. Search for something like "*mssql*entitycommand*.class" within your JBoss directory structure. I also cannot remember which jar it's in, but you should be able to find it quite easily (as long as you have JBoss 3.2.1 - it was not included before). Just make sure you specify the entire package hierarchy in the entity-command tag.


          That should do it. If it doesn't work, post it and I'll try to help you when I'm back.

          Good luck,
          Daniel

          • 2. Re: JBOSS 3.2.1 MSSQL Datasource problem
            Daniel Lehmann Newbie

            Qian,

            I posted an auto-increment example earlier today, but thought the post got lost. Just realized it is actually on now. You can read the detailed approach (including ant and xdoclet ) here, including the entity-command class I couldn't remember:

            http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t= me know if it worked... ;)

            Daniel