Problems with transaction scope time out

Hi, i'm trying to run wrap a transaction around a block of code to make sure every statement is carried out. Here's what i have:

using (TransactionScope ts =newTransactionScope())

{
...
ts.Complete();
}

The code executed in the ... part is a set of sql commands within my dal. If this above expression takes less than 30 seconds everything is carried out correctly, however if it takes longer than 30 seconds i receive an sql timeout error. I tried modify the above code to:

using (TransactionScope ts =newTransactionScope(TransactionScopeOption.Required,TimeSpan.FromMinutes(5)))

{
...
ts.Complete();
}

But it did not seem to make a difference. I suppose the easiest option would be to increase the timeout for every sql command in my dal but i feel that the 30 second timeout is sufficient for a single command.

Appreciate if someone could tell me how this should be done correctly. Thanks

Try to run the same proc/T-SQL from query analyzer within a transaction and see how long it takes. Try to tune your queries to run under 1-2 secs.

|||Hi, i made the sql as efficient as possible but still no joy so i figured that it wasn't a good idea to wrap a transaction around what i was doing. I simply logged whether the process had been completed and all is well.