Board index » delphi » SQL Server transaction nesting

SQL Server transaction nesting

Why can't I nest transactions in SQLServer 2000?
A call to the following procedure raises an exception

ADOConnection.BeginTrans
....
...
ADOConnection.BeginTrans  <-- EXCEPTION

Do I have to setup ADO or SQLServer for a nesting level higher than 1?

Thanks,
Juan

 

Re:SQL Server transaction nesting


Quote
>Why can't I nest transactions in SQLServer 2000?
>A call to the following procedure raises an exception

I believe you can only nest transactions in stored procedures
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:SQL Server transaction nesting


I think MS theory is that if you need to roll back a transaction you should
roll back the entire transaction ,,, ie .. if adding details fails after
adding a master , rolling back the details alone does not make sense ...
especially  according to strict database theory as advanced by Codd and
others at IBM many years ago.

Re:SQL Server transaction nesting


Last night, searching in the net, I found that, although SQLServer 2000
allows nesting transaction, ADO is limited to only one transaction per
session.
Can this be simulated via SQL statements? For example, running a ADOCommand
with the sentence "BEGIN TRANSACTION", executing some code, and the running
another ADOCommand with "COMMIT TRANSACTION".

Thanks!.

Quote
"Del Murrray" <del.mur...@credithawk.net> wrote in message

news:3d10813d$1_2@dnews...
Quote
> I think MS theory is that if you need to roll back a transaction you
should
> roll back the entire transaction ,,, ie .. if adding details fails after
> adding a master , rolling back the details alone does not make sense ...
> especially  according to strict database theory as advanced by Codd and
> others at IBM many years ago.

Re:SQL Server transaction nesting


Quote
On Wed, 19 Jun 2002 11:34:36 -0300, "Juan" <nore...@nospam.com> wrote:
>Last night, searching in the net, I found that, although SQLServer 2000
>allows nesting transaction, ADO is limited to only one transaction per
>session.
>Can this be simulated via SQL statements? For example, running a ADOCommand
>with the sentence "BEGIN TRANSACTION", executing some code, and the running
>another ADOCommand with "COMMIT TRANSACTION".

Yes, from the look at the Knowledgebase, this seems to be MS'
recommended approach if you need this...

Other Threads