Nest transactions in SQLServer

[mahongxi]

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/default.aspx?scid=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.


----------------------------------------------------------
这个贴是先发在新闻组里的,所以就没用中文,大家凑合着看看吧. :-)

[zjcxc]

英文差,不是很确定楼主要实现什么功能

[---涛声依旧---]

SQL Server不支持事務嵌套
只好找微軟了

[wyb0026]

sqlserver 嵌套事务呀

[zjcxc]

嵌套事务是知道的,只不过看不清楚具体的处理要求

[zjcxc]

--猜测,楼主大概是要实现类似这样的功能

create table #t(a int primary key)
go

begin tran
insert #t select 1
union all select 2 --处理1
save tran pos1 --保存事务点

update #t set a=1 --处理2
if @@error<>0
begin
print '错误'
rollback tran pos1 --如果处理2出错,回滚到处理1的结果处
end
commit tran --提交成功的事务
go

--显示结果
select * from #t
go

drop table #t

[mahongxi]

zjcxc(邹建) 正解,本人愚蠢之极,没查帮助就断定SQLSERVER里没有SAVE TRANS
谢谢大家了.

[yjzhg]

up, first