0 Replies Latest reply on Feb 11, 2003 6:59 AM by Ivan Vaghi

    JMS Queue Persistence on SQLServer2000 - Solution

    Ivan Vaghi Newbie

      In the past we had some problems persisting JMS queues to SQL2000. We looked at the code, we found the rigth parameters and we finaly made it.

      Here is the solution:

      + Install Microsoft JDBC for SQLServer2000
      + Copy JARS
      Add jars in MSSQL2000/lib to JBOSS/server/default/lib

      + Configure SQL Server

      - Enable SQL Authentication

      If you are using SQL Server 2000, you need to go to the SQL Server Enterprise Manager.

      You should see a tree similar to this:

      Console Root
      ->MS SQL Server
      --->SQL Server Group
      ------><Machine Name>(Windows NT)

      Right click on <Machine Name>(Windows NT)
      Choose "Properties"
      That should bring up the "SQL Server Properties Configure" Page.
      Choose the "Security" Tab
      You should see 2 radio buttons under Security Authentication
      ---> 1. SQL Server and Windows
      ---> 2. Windows only


      Choose option 1.



      Create New Database

      Create a Database called TL_JBoss_JMS
      (just an example name - you can put what you want, just keep it consistent with the naming further on...)

      Create New Data Type

      On the EnterpriseManager, under the database TL_JBoss_Persistent_Queue on the SQLServer2000, create a new user data type mapping OBJECT to binary fixed size [8000]


      + Configure JBOSS deployment files

      In the deployment descriptor add to the JDBC connection string: SelectMethod=cursor

      In :

      Change OBJECT to IMAGE

      assign BLOB_TYPE=OBJECT_BLOB

      modify jbossmq-service.xml :



      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
      <depends optional-attribute-name="DataSource">jboss.jca:service=LocalTxDS,name=MSSQLDS
      jboss.jca:service=LocalTxCM,name=MSSQLDS

      BLOB_TYPE = BINARYSTREAM_BLOB
      INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
      INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
      SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
      SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      MARK_MESSAGE = UPDATE JMS_MESSAGES SET (TXID, TXOP) VALUES(?,?) WHERE MESSAGEID=? AND DESTINATION=?
      DELETE_ALL_MESSAGE_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID=?
      DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
      DESTINATION VARCHAR(50) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
      MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )



      modify mssql-service.xml:

      <depends optional-attribute-name="ManagedConnectionFactoryName">
      <!--embedded mbean-->


      MSSQLDS



      <config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://WSITITAFMD002:1433;DatabaseName=TLTest;SelectMethod=cursor</config-property>
      <config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
      <!--set these only if you want only default logins, not through JAAS-->
      <config-property name="UserName" type="java.lang.String">ivan</config-property>
      <config-property name="Password" type="java.lang.String">test</config-property>




      <!--Below here are advanced properties -->
      <!--hack-->
      <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper