Transactions in dotNet 2.0

[Allan Ebdrup]

I have a transaction in dotNet 2.0:
-------------
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
options.Timeout = new TimeSpan(3, 0, 0); //maximum 3 hour transaction
using (TransactionScope transactionScope = new
TransactionScope(TransactionScopeOption.Required, options))
{
....
}
-------------
When it runs I have a sql query that fails some times, but I catch the
exception and correct the problem. When I continue I get the error
"the operation is not valid for the state of the transaction." so the
transaction has failed because the sql query failed.
How can I get the transaction to ignore the error and continue running?
Do I have to write code that never throws an SQL exception?

Kind Regards,
Allan Ebdrup

[Nicholas Paldino [.NET/C# MVP]]

Allan,

Pretty much. For SQL server, once you fail on a connection, the part of
the transaction related to SQL Server will never commit.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Allan Ebdrup" <eb****@noemail.noemailwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...

>I have a transaction in dotNet 2.0:
-------------
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
options.Timeout = new TimeSpan(3, 0, 0); //maximum 3 hour transaction
using (TransactionScope transactionScope = new
TransactionScope(TransactionScopeOption.Required, options))
{
...
}
-------------
When it runs I have a sql query that fails some times, but I catch the
exception and correct the problem. When I continue I get the error
"the operation is not valid for the state of the transaction." so the
transaction has failed because the sql query failed.
How can I get the transaction to ignore the error and continue running?
Do I have to write code that never throws an SQL exception?

Kind Regards,
Allan Ebdrup

[Steven Cheng[MSFT]]

Hi Allan,

Yes, as Nicholas has mentioned, the problem here is that you try execution
further commands over a connection where an exception/failure has already
occur. When you using the TransactionScope to manage transaction, the .NET
transaction engine will help you automatically deal with the underlying
transaction on each connection(in the scope), whenever you failed on a
connection, you'll not be able to execute and commit command against that
connection. For your scenario, I suggest you consider create and open a
new connection after exception occurs and you handled it. Thus, the
sequential execution on the new created connection can also continue and
commit (within the same TransactionScope)

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

[Allan Ebdrup]

"Steven Cheng[MSFT]" <st*****@online.microsoft.comwrote in message
news:cR**************@TK2MSFTNGHUB02.phx.gbl...
<snip>

For your scenario, I suggest you consider create and open a
new connection after exception occurs and you handled it. Thus, the
sequential execution on the new created connection can also continue and
commit (within the same TransactionScope)

Thank you for the feedback, I've already changed the application so no SQL
exceptions should occur.
But your information is very valuable.

Kind Regards,
Allan Ebdrup

[Steven Cheng[MSFT]]

You're welcome :)

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.