Sql transaction failure

Question

I am getting below error while inserting records in database.

System.Transactions.TransactionException: The operation is not valid for the state of the transaction. ---> System.TimeoutException: Transaction Timeout
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

Actually i am inserting data in database by transaction scope method, code for which is described below.

TransactionOptions tOptions = new TransactionOptions();
tOptions.IsolationLevel = IsolationLevel.ReadCommitted;
tOptions.Timeout = TransactionManager.MaximumTimeout;
using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, tOptions))
{
}

Is there anything incorrect in above statement?

One thing to mention here is that i am bulk inserting data in atleast 10 tables with lot of records and tables also allow duplicate records to be inserted in bulk insert, Syntax used to achieve this is mentioned below.

CREATE UNIQUE INDEX Index_a ON table1([c1], [c2]) WITH IGNORE_DUP_KEY

Will be thankful if anyone could help me with this issue.

Try putting your code in a try-catch block and inspect the exception in the debugger. I've done this and sometimes gotten more detail on the problem, like a NULL being inserted in a non-nullable field, that sort of thing. — Melanie, Apr 18 '13 at 21:03
Does your operation succeed if you run it without the transaction? If so, how long does it take to complete without the transaction? — Nathan, Apr 18 '13 at 21:07
possible duplicate of ["The operation is not valid for the state of the transaction" error and transaction scope](http://stackoverflow.com/questions/193154/the-operation-is-not-valid-for-the-state-of-the-transaction-error-and-transact) — Andrew Savinykh, Apr 18 '13 at 23:09
Do you have multiple transactions ? Maybe another transaction is running and so doesn't allow multiple transactions... resulting in a timeout. — Virus, Apr 19 '13 at 09:52
Check this out: [http://stackoverflow.com/questions/7006806/transactionscope-transactionaborted-exception-transaction-not-rolled-back-sho][1] [1]: http://stackoverflow.com/questions/7006806/transactionscope-transactionaborted-exception-transaction-not-rolled-back-sho — ZooZ, Jun 26 '13 at 07:44

score 1 · Accepted Answer · answered Jun 27 '13 at 04:16

1

The problem was that the MSDTC service was disabled and I was using bulk db operations. After enabling it the problem was resolved.

answered Jun 27 '13 at 04:16

Rajat Saini

557
7
26

score 0 · Answer 2 · answered Apr 22 '13 at 17:30

0

It seems like you have uncommitted transactions. Try restarting the Sql Server, or Oracle service.

answered Apr 22 '13 at 17:30

Aidin

2,134
22
26

Sql transaction failure

2 Answers2