nesting transactions
Jerry J
I'm not sure if this is a SQL Server or .net question so I appologize if
this is in the wrong news group...
I have two sql server stored procedures that use transactions. Both stored
procedures have there own begin transaction , commit and rollback.
Currently I have .Net code that executes both of these stored procedures
consecutively. Everything works fine unless the second stored procedure has
an error and rolls itself back. The resulting issue is that the first stored
procedure is already committed, but it also should have been rolled back
because the second one fails.
One way I could fix this, of course, is to merge the two into a single
stored procedure. However, because the the second stored procedure is used by
other parts of our system, I am hesitant.
As another solution, I am thinking that I can nest the two stored procs
inside an ADO.Net transaction as in the pseudo code (I know the syntax is not
correct but you can get the picture) below.
Can someone tell me if this is valid?
fmConn.Open();
SqlTransaction txn = fmConn.BeginTransaction();
try
{
ExecuteQuery("StoredProc1"); // has internal transaction
ExecuteQuery("StoredProc2"); // has internal transaction
txn.Commit();
}
catch
{
txn.RollBack();
}
Can someone tell me of there is another way to handle this? Is it possible
to use transactions via ADO.net (SqlTransaction tran =
conn.BeginTransaction();
) that can encapsulate the two stored procedures?
You input will be appreciated.
--
Jerry J
--
Jerry J
rpresser
> Can someone tell me of there is another way to handle this? Is it possible
> to use transactions via ADO.net (SqlTransaction tran =
> conn.BeginTransaction();
> ) that can encapsulate the two stored procedures?
>From my understanding, using the transaction commands with ADO.net is
exactly equivalent to issuing extra BEGIN TRANSACTION / COMMIT
TRANSCATION statements. In other words, doing what you typed in
ADO.NET is the same as if you'd typed this in Query Analyzer:
SET XACT_ABORT ON
BEGIN TRANSACTION
EXECUTE StoredProc1
EXECUTE StoredProc2
COMMIT TRANSACTION
Jerry J
--
Jerry J