Hi Nemachtiani,

Thanks, that is much more helpful 🙂

I suspect that something is causing the transaction to rollback in the SQL SP, which is causing a problem with the .NET transaction handler.

I can't say why or how, because I am not debugging the code, but that is my suspicion.(It is a known effect that a rollback in the SQL code can cause the .net transaction to lose it's connection)

I suggest that you do not try to handle rolling back the transaction within the SP as long as you are using the .net SQLTransaction.

Add some logging to your exception handler (you may already have it there, but left out for brevity) to record the contents of the errors collection from the SQLConnection object:

Here:

Catch ex As System.Exception

' Add some error logging from cn.Errors

Try

'when rare case ocurrs this line DONT THROW any further exception

tran.Rollback()

Catch exc As System.Exception

ExceptionManager.Publish(exc)'never happens when rare case ocurr

End Try

Finally

That extra logging should help to identify the issue, but if it is the rollback inside the SP, you will probably just see an invalid connection error or something like that indicating that the transaction has lost it's connection.

Oh, and do change that ByVal to a ByRef, won't you?

Good luck ! 🙂