A suggestion from a user regarding MSSQL and the jdbc2 pm configuration.

When using JBoss JMS with SQL Server, we often see the following error message:

13:03:33,031 INFO [STDOUT] org.jboss.mq.SpyTransactionRolledBackException: Transaction was rolled back.; - nested throwable: (org.jboss.mq.SpyJMSException: Could not mark the message as deleted in the database: update affected 0 rows)

We managed to track this issue down to the way in which the JMS_MESSAGES table was being created. Sometimes, SQL Server requires a CLUSTERED index on a table to overcome this issue. Our modified JMS_MESSAGES schema looks like this:

CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB BLOB);

CREATE UNIQUE CLUSTERED INDEX JMS_MESSAGES_IDX ON JMS_MESSAGES (MESSAGEID, DESTINATION);

CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID);

CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION);

Basically, we pulled the original PRIMARY KEY clause out of the table create (which will create a unique index) and replaced that with an explicit create of a unique clustered index.

The following msdn article talks about indexing:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp