Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."
Here follows the code. Hopefully anybody can help me with this problem.
Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
create trigger td_thmld2 on thmld2 for delete as
select @numrows = @@rowcount
if @numrows = 0
select @numrows = (select count(*) from trigstat with (nolock) )
if @numrows > 0
save transaction trans_td_thmld2 <-- REASON FOR ERROR?
/* delete all children in "thmstat" */
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode
/* delete all children in "thmldlk1" */
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode
/* errors handling */
raiserror @errno @errmsg
rollback transaction trans_td_thmld2