Category Archives: MS SQL

System.Transactions.TransactionException: The operation is not valid for the state of the transaction

When running large amounts of data into an MS SQL database via a composite WCF-SQL send port in BizTalk I would eventually get (after about 20mins) the error “System.Transactions.TransactionException: The operation is not valid for the state of the transaction”. Smaller amounts of data ran fine.

The solution was to turn off the transaction in the send port. Of course this means you lose the transactional capabilities but do you really want to lock a table on a live database for 20 mins anyway? It may be good practice to only turn this feature off when you know you are running a long transaction and then turn it back on again when you are finished.

Turn_Off_Transactions_In_Send_Port

BizTalk WCF-SQL Transactions Hanging

I’ve had a lot of trouble recently where MS SQL database transactions never seem to finish when useAmbientTransaction is set to true in the WCF-SQL adapter. What made the problem more infuriating is that it didn’t always happen and was much more frequent in my development environment. I was trying to follow the method detailed in Stuart Brierley’s excellent blog article which describes how to execute multiple procedures/inserts/updates in one message and transaction. I’ve also experienced the same problem when transactions are declared within stored procedures BizTalk is trying to execute.

Whilst all this was going on I had noticed but discounted this error appearing in the windows event viewer.

The following stored procedure call failed: ” { call [dbo].[bts_UpdateMsgbox_SendHost]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. SQL Server returned error string: “Warning: The join order has been enforced because a local join hint is used.;Warning: The join order has been enforced because a local join hint is used.;Duplicate key was ignored.”.

I had originally ignored the error mistakingly thinking it wasn’t really relevant. Why would an error on the BizTalk database have any impact on the transaction taking place on the application database?

I eventually found that this error was causing the entire transaction to hang, or at least never actually commit to the database even though SQL Agent trace proved that the queries were running all the way through to completion. The connection would just sit there, idle and open forever and there would be a lock on the database table I was trying to update/insert into. The only way of releasing the transaction would be to sp_who and then kill <PID>.

Reading more into my problems this error is releated to performance issues in the environment which is entirely plausible based on the modest hardware specs of my development VM. The solution for me was to turn off tracking on my send/receive ports. I have also read that running this SP can also help.

DECLARE @
RC int
DECLARE @fLeaveActSubs int

SET @fLeaveActSubs = 1

EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox]
@fLeaveActSubs
GO