Nabil,

>In this case the Begin/Rollback/End transaction commands must be used in the front-end not in SPs.<

Yes, but it is still a SQL Server transaction, because you do it against the connection, like this:

SqlTransaction oTrans = oConnection.BeginTransaction();
SqlCommand sc = new SqlCommand();
sc.CommandType = CommandType.StoredProcedure;
sc.Connection  = oConnection;
sc.Transaction = oTrans;

//--- all your update calls go here, they will all be part of the transaction
//    set a variable to indicate completion

if (IsOK == true)
{  
    oTrans.Commit();  
}
else
{
    oTrans.Rollback();
}

oConnection.Close();
return IsOK;

So, SQL Server itself would still manage the transaction and would roll it back correctly in the event of a power failure or lost connection, as Mike said.

~~Bonnie



>
>
>>Thanks for jumping in and verifying this Mike. I knew it was no big deal to SQL Server to recover from stuff like this, I just wasn't sure how or when it was implemented.
>>
>>~~Bonnie
>>
>>
>>>As long as the mutliple updates are within a transaction...
>>>
>>>if just the connection to the server is lost, SQL Server will detect this and rollback the transaction.
>>>
>>>if SQL Server were to go down, the transaction would be rolled back when SQL Server restarted. When SQL Server starts, it goes through "recovery." Durning this phase, SQL Server looks for completed transactions that were not written to disk and rolls those forward. It also looks for incomplete transactions that were written to disk and rolls those back.
>>>
>>>-Mike
>>>
>>>>But SP's are more efficient because SQL Server compiles the SP's only once, and therefore little server resources are used.
>>>>
>>>>As far as network failure during updating ... you always will wrap parent-child table updates in a transaction, so recovering from a power failure is something that can be done easily enough in SQL Server. I don't know the particulars of doing database recoveries ... I'm not a DBA and don't know all the ins and outs of SQL Server, but I *do* know that it's no big deal.
>>>>
>>>>~~Bonnie
>>>>