Nest transactions in SQLServer

hi guys:
i am doing a hard work of migrating programs from Oracle to SQL-Server.
i encounte a problem about transaction which hardly hurt my heart (forgive
my poor english first ... :-) )
you know,there is such a usage in oracle:
SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACKTOSAVEPOINT xx
end if;
so, if things 2 failed, the data altered by things1 is guaranteed to be
rollback.
but when i change it to fit sql-server, i was happily wrote such codes:
BEGIN TRANS... //must
...
...
BEGIN TRANS... //SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
end if;
..
...
COMMIT TRANS.
yes, i got a error: 'Cannot start more transactions on this session' (oh,
these codes are under ado in delphi)
i find the answer in msdn
CAUSE
By design, OLE DB Provider for SQL Server does not allow nested
transactions.
http://support.microsoft.com/defaul...kb;en-us;316872
but,i think, such nest transcations (or same other trick equal to oracle
save point) is very needed in general business process.
how to solve this problem?
my best wishes.Hi
Have you looked up SAVE TRANSACTION in BOL?
John
"MaHahaXixi" wrote:

> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart (forgi
ve
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||hi john:
thank u for ur response.
what do u mean by BOL' sorroy, i am just a greenhand.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||oh... yes i got the answer
there are
save trans {savepoint}
rollack trans {savepoint}
in t-sql too.
thanks all the same
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3fh2p$vuo$1@.mail.cn99.com...
> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart
(forgive
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||thank you john, i got it .yes, save transactions
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||BOL = SQL Server Books Online, found in the SQL Server program group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message news:d3ftsf$137e$1@.mail.cn99.com...

> hi john:
> thank u for ur response.
> what do u mean by BOL' sorroy, i am just a greenhand.
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> SQL-Server.
> (forgive
> be
> codes:
> (oh,
>|||Hi
You may find these articles useful:
http://www.microsoft.com/resources/...r />
0761.mspx
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
John
MaHahaXixi wrote:
> oh... yes i got the answer
> there are
> save trans {savepoint}
> rollack trans {savepoint}
> in t-sql too.
> thanks all the same
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
> news:d3fh2p$vuo$1@.mail.cn99.com...
SQL-Server.
> (forgive
to be
codes:
session' (oh,
oracle|||thanks a lot :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23uTmv2zPFHA.3928@.TK2MSFTNGP09.phx.gbl...
> BOL = SQL Server Books Online, found in the SQL Server program group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3ftsf$137e$1@.mail.cn99.com...
to
oracle
>|||thank you! very useful to me!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113307105.002786.136380@.f14g2000cwb.googlegroups.com...
> Hi
> You may find these articles useful:
>
http://www.microsoft.com/resources/...art2/c0761.mspx

> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> John
> MaHahaXixi wrote:
> SQL-Server.
> to be
> codes:
> session' (oh,
> oracle
>