Gentran Integration Suite?version 4.3.??MS SQL 2005 Database. The?procedure below?was used to remove old data from the GIS *_GUID tables.

Executed the following SQL statements:

select top 1 * into ACT_SESSION_GUID_TEMP from ACT_SESSION_GUID order by NEXT_SESS_ARC_ID desc
select top 1 * into MBX_MESSAGE_GUID_TEMP from MBX_MESSAGE_GUID order by NEXT_MESSAGE_ID desc
select top 1 * into DATA_FLOW_GUID_TEMP from DATA_FLOW_GUID order by NEXT_FLOW_ID desc

drop table ACT_SESSION_GUID
drop table MBX_MESSAGE_GUID
drop table DATA_FLOW_GUID

Rname all Temp Tables to original table names.

The SELECT INTO command should automatically recreate the?tables with the same data types?for?the columns?of new "TEMP" tables.

in wf.log

[2010-12-20 13:06:18.278] ERROR 000000000000 GLOBAL_SCOPE com.sterlingcommerce.woodstock.workflow.queue.WorkFlowQueueListener.onMessage() caught Exception for workflow 36683162
SQL Error Code: 3971
SQL State: S0001
com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:5d000008ec.
?at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
?at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)

system log:

[2010-12-20 12:39:18.814] ERROR 000310100650 UTIL.FRAME_JDBC.ERR_NO_MSG_EXCEP12 NO_MSG_EXCEP
SQL Error Code: 3971
SQL State: S0001
com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:6e000000ef.
?at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)

noapp log:

[2010-12-20 13:04:52.762] ALL 000000000000 GLOBAL_SCOPE SQLexception in XAResourceWrapper.rollback()
[2010-12-20 13:04:52.762] ALL 000000000000 GLOBAL_SCOPE com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:5d000008ec.
[2010-12-20 13:04:52.762] ALL 000000000000 GLOBAL_SCOPE

The SQL Errors occurred after the restart of the server.

The issue was caused when?the SELECT INTO commands?recreated the Table with the same data types for each column it didn't recreate the Primary Key fields for each of those tables.

It was necesary to?manually?use the SQL Management Studio?and set the first field on each of the tables as a Primary Key.

The change is dynamic so there is no need to restart GIS. After the change the error stopped occuring.

- MBX_MAILBOX_GUID.html