Professional Web Applications Themes

Trigger problem!! New transaction cannot enlist..... - Microsoft SQL / MS SQL Server

I have two window 2003 servers machines, each one has sql 2000 server installed in it. BOTH servers are on same domain!! I have a trigger on SERVERB, so that everytimes something get updated/deleted/inserted on manager table, it will fire the trigger and delete same thing from manager table on SERVERA (Linked server) ---------------------------------------------------------- CREATE TRIGGER managers_tr_iud ON [dbo].[managers] FOR INSERT, UPDATE, DELETE NOT FOR TION AS SET XACT_ABORT ON SET NOCOUNT ON delete SERVERA.wrback.dbo.managers from SERVERA.wrback.dbo.managers RO JOIN deleted D ON RO.manager_num = D.manager_num if not exists (select * from SERVERA.wrback.dbo.managers where manager_num = (select manager_num from inserted)) insert ...

  1. #1

    Default Trigger problem!! New transaction cannot enlist.....

    I have two window 2003 servers machines, each one has
    sql 2000 server
    installed in it.

    BOTH servers are on same domain!!


    I have a trigger on SERVERB, so that everytimes something
    get
    updated/deleted/inserted on manager table, it will fire
    the trigger
    and delete same thing from manager table on SERVERA
    (Linked server)

    ----------------------------------------------------------
    CREATE TRIGGER managers_tr_iud ON [dbo].[managers]
    FOR INSERT, UPDATE, DELETE NOT FOR TION
    AS
    SET XACT_ABORT ON
    SET NOCOUNT ON

    delete SERVERA.wrback.dbo.managers
    from SERVERA.wrback.dbo.managers RO JOIN deleted D ON
    RO.manager_num =
    D.manager_num

    if not exists (select * from
    SERVERA.wrback.dbo.managers where manager_num
    = (select manager_num from inserted))
    insert SERVERA.wrbackdbo.managers select * from inserted

    --------------------------------------------------------
    I don't know why I got this error. I have
    administrator/sa account, I
    also try to use regular delete/insert/update statements
    instead of trigger,
    it works fine but just Trigger doesn't work. I thought
    there is something
    wrong with MSDTC, but I checked service in
    administrator Tool,
    DTC is running on both machine. Do you know what is
    going wrong?



    Server: Msg 7391, Level 16, State 1, Procedure
    managers_tr_iud, Line 7
    The operation could not be performed because the OLE DB
    provider 'SQLOLEDB'
    was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot
    enlist in the
    specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    ITransactionJoin::JoinTransaction returned 0x8004d00a].







    Mike Guest

  2. #2

    Default Re: Trigger problem!! New transaction cannot enlist.....

    Remove "SET XACT_ABORT ON" from the trigger. Also, you might want to change
    your query to

    if exists(select * from SERVERA.wrback.dbo.managers a join inserted i on
    a.manager_num=i.manager_num)


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Mike" <com> wrote in message
    news:80ec01c35a9e$ac6d9060$gbl... 


    oj Guest

  3. #3

    Default Re: Trigger problem!! New transaction cannot enlist.....

    See Bill's detailed response on a question similar to yours & see if it
    helps.
    http://groups.google.com/groups?selm=Pp2fEH%24NCHA.2496%40cpmsftngxa07

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: Trigger problem!! New transaction cannot enlist.....

    Here is the rewrite of your trigger... see if this helps...

    CREATE TRIGGER managers_tr_iud ON [dbo].[managers]
    FOR INSERT, UPDATE, DELETE NOT FOR TION
    AS
    if rowcount=0 return

    --zap old data
    delete RO
    from SERVERA.wrback.dbo.managers RO
    where exists(select * from deleted D where RO.manager_num =D.manager_num)

    --insert
    insert SERVERA.wrbackdbo.managers
    select *
    from inserted I
    where not exists(select * from SERVERA.wrbackdbo.managers M where
    M.manager_num=I.manager_num)
    GO



    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Mike" <com> wrote in message
    news:phx.gbl... 
    > change 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

  5. #5

    Default Re: Trigger problem!! New transaction cannot enlist.....

    Anith,

    I can do select * to call linked server with no problem. which mean
    there is no network problem,
    but looked like a permission problem. However,
    I'm using SA account on both servers, that is weird.
    I checked the permissions, it's db_owner, and has access to all dbs. I
    also confirmed that port 1433 and 135 are openned on both servers. I
    checked both servers,
    client utility network is using dynamically determine port.
    I don't know if that's the issue... is it the same if I put 1433 in
    inputbox?



    "Anith Sen" <com> wrote in message
    news:phx.gbl... 


    Mike Guest

  6. #6

    Default Re: Trigger problem!! New transaction cannot enlist.....

    Oh, I can even do simple delete statement from linked server,

    delete from SERVERA.wrback.dbo.managers

    it works fine.



    but if I delete from current server, then when trigger tried to delete the
    row from linked sever, trigger failed. I think it's trigger problem.

    delete from wrback.dbo.managers

    The operation could not be performed because the OLE DB provider 'SQLOLEDB'
    was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the
    specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    ITransactionJoin::JoinTransaction returned 0x8004d00a].

    "Anith Sen" <com> wrote in message
    news:phx.gbl... 


    Mike Guest

  7. #7

    Default Re: Trigger problem!! New transaction cannot enlist.....

    nope... same error.


    "Anith Sen" <com> wrote in message
    news:phx.gbl... 
    Try: 


    Mike Guest

  8. #8

    Default Re: Trigger problem!! New transaction cannot enlist.....

    Have you checked to see if there are any existing firewalls in the server. I
    would imagine any stored procedures/triggers using DTC will have this
    problem. One option I would suggest is to use DTCPING to see if you can ping
    the servers directly. You can download this utility from:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306843
    Also to configure the DTC through a firewall, refer to:
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q250367

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  9. #9

    Default Re: Trigger problem!! New transaction cannot enlist.....

    i RUN dtcping.exe ,
    I got this message:

    .......................
    Invoking RPC method on B-C3P1 (that's the server)
    Problem:fail to invoke remote RPC method
    Error(status=1753): at dtcping.cpp (Line:265)

    RPC pinging failure
    Description:1753-There are no more endpoints available from the endpoint
    mapper.
    Invalid partner CID while constructing binding handle

    ...................

    I do dtcping to other sql servers, I always get this error message.
    However, my other sql servers does work with trigger even though it gets
    this error message


    "Anith Sen" <com> wrote in message
    news:phx.gbl... 

    ping 


    Mike Guest

Similar Threads

  1. disable trigger from transaction
    By Postgres General in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 25th, 11:19 AM
  2. Replies: 5
    Last Post: September 18th, 09:15 AM
  3. Problem with transaction
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 02:59 PM
  4. advanced: Transaction-logging Trigger
    By vsiat in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: March 18th, 11:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139