Symptom

java.sql.SQLException: [IBM][SQLServer JDBC
Driver][SQLServer]The server
failed to resume the transaction. Desc:3500000001.

This error may occur if you attempt to deploy Process Commander using any of the following Microsoft SQL Server 2005 JDBC drivers and application servers:

• Microsoft SQL Server JDBC driver versions 1.2, 2.0, 3.0, 3.5, 3.6, and 3.7.
• WebSphere 6.1 FixPack 6.1.0.15 and Microsoft SQL Server 2005, using the Microsoft JDBC drivers v 3.6 or later ("Data Direct") distributed by IBM.

This error can also occur in a running Process Commander system if you upgrade to WebSphere 6.1 FixPack 6.1.0.15.

• WebLogic 9x and Microsoft SQL Server 2005, using JDBC Driver 2.0.
• WebLogic 9x and Microsoft SQL Server 2005, using JDBC Driver 3.0.

The exception occurs when the WebSphere application server datasource connection manager attempts to process a PreparedStatement.close() method call for a stored procedure that uses transaction management commands (BEGIN TRANSACTION, COMMIT, or ROLLBACK).

Subsequent (unrelated) SQL queries that use the database connection that first encounters the error may also fail with the "failed to resume transaction" error, because the earlier procedure execution is in an improperly closed state.

Solution

Two alternatives are available:

• Use Process Commander's direct database connectivity rather than the WebSphere facility.
• Downgrade the JDBC drivers. Replace the JDBC driver file with Version 3.5 obtained from IBM following a link from the IBM page identified below..

Pegasystems does not recommend downgrading to the Microsoft SQL Server JDBC v1., as it has known issues.

Discussion

See IBM's Authorized Program Analysis Report (APAR) at PK64012: SQLServer SQLException: The server failed to resume the transaction..

It seems the embedded SQLServer JDBC drivers, versions 3.6 and 3.7, do not allow a transaction within a stored procedure.

Process Commander 5.X uses stored procedures (for example sppr_aggregate_usage and sppc_data_uniqueid) that include transaction control logical and so require this capability.