Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    The Netherlands, Geleen
    Posts
    8

    Unanswered: Delete trigger & COM+

    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.

    SQL-statement:
    Code:
    Function getDeleteRequestSQL(ByRef strRequestId As String) As String
        Dim strSQL As String
        strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
        getDeleteRequestSQL = strSQL
    End Function
    And then the place where the error occurs.
    Code:
         GetConnection cnConn
         strSQL = getDeleteRequestSQL(reqId)
    10  cnConn.Execute strSQL, , adExecuteNoRecords
    And finaly the trigger:
    Code:
    create trigger td_thmld2 on thmld2 for delete as
    begin
      declare
          @numrows  int,
          @errno    int,
          @errmsg   varchar(255)
    
          select  @numrows = @@rowcount
          if @numrows = 0
             return
    
          select  @numrows = (select count(*) from trigstat with (nolock) )
          if @numrows > 0
             return
    
          save transaction trans_td_thmld2 <-- REASON FOR ERROR?
         
          /*  delete all children in "thmstat"  */
          delete thmstat
          from   thmstat t2, deleted t1
          where  t2.hdmcode = t1.hdmcode
          
          /*  delete all children in "thmldlk1"  */
          delete thmldlk1
          from   thmldlk1 t2, deleted t1
          where  t2.hdmlmldcode = t1.hdmcode
    
    /*  errors handling  */
    error:
        raiserror @errno @errmsg
        rollback  transaction trans_td_thmld2
    end

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't know what that is, but it has nothing to do with the trigger...

    Well that me guessing again...

    Sounds more like you're establishing a tran across many servers....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    The Netherlands, Geleen
    Posts
    8
    The components I've created are all located within one dll. The dll is active in the component services on my own webserver and is working fine. So the action isn't distributed over the network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •