CODEWITHSUNDEEP
c#sqlsql-servertransactions
Viper
Viper

Reputation: 2236

Transaction Timeout

i have a problem with the following exception:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

The 'call' against the database takes 10m 53s (tested without a transaction and succeded) and with the transaction it throws the exception after 10m. So i'm pretty sure it's not the code which produces the problem but the transaction timeout limit.

I've seen that many people struggled with this problem, but i've tried all the possible solutions i have found but still have no success to get rid of this exception.

Following code do i use:

[...]
TransactionOptions transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = IsolationLevel.Snapshot;
transactionOptions.Timeout = new TimeSpan( 0, 0, 30, 0, 0 );

using( var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions) )
{
     foreach (var selector in GetDataContext().sp_GetChangedSelectors(changeSet))
     {
          // xml-result will be stored in a file
     }

     // multiple other SP calls the same way

     scope.Complete( );
}

I added the statement

<system.transactions>
    <machineSettings maxTimeout="02:00:00"/>
</system.transactions>

to the machine.config in %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\Config on both machines (local and sql-server-machine).

I'm running out of ideas what i could try next. Any suggestions?

Upvotes: 4

Views: 1464

Answers (2)

ken2k
ken2k

Reputation: 48985

Make sure you modified the right machine.config file.

Depending on how you compiled your application (AnyCPU/x64/x86, "prefer 32-bit"), the machine.config used will be different (Framework vs Framework64).

Also make sure you reboot your machine after modifying machine.config.

Upvotes: 1

toATwork
toATwork

Reputation: 1367

Just a wild guess: are you running a 32 bit appication? Then this might be the wrong machine.config!

%WINDIR%\Microsoft.NET\Framework\v4.0.30319

otherwise I would check if this value is override in the web.config or app.config

Upvotes: 1

Related Questions