Professional Web Applications Themes

trigger error - Microsoft SQL / MS SQL Server

Hi, I'm getting the following error when trying to fire a trigger to a different server: 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]. Original trigger definition: CREATE trigger trg_InsertNiobeRob on dbo.tblRob for insert as set xact_abort on declare Id int select Id = ID from inserted begin tran insert NIOBE.MSStats.dbo.Summary select * from inserted where ID = ID commit tran MSDTC is started ok. ...

  1. #1

    Default trigger error

    Hi,

    I'm getting the following error when trying to fire a
    trigger to a different server:

    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].

    Original trigger definition:


    CREATE trigger trg_InsertNiobeRob on dbo.tblRob

    for insert

    as

    set xact_abort on

    declare Id int

    select Id = ID
    from inserted

    begin tran
    insert NIOBE.MSStats.dbo.Summary
    select * from inserted
    where ID = ID
    commit tran

    MSDTC is started ok. Both servres are SQL 2000

    Can anyone help?

    Thanks

    Elisabeth


    Elisabeth Smith Guest

  2. #2

    Default Re: trigger error

    Elisabeth

    instead of BEGIN TRNAS
    try using BEGIN DISTRIBUTED TRANSACTION
    in your stored procedure

    hth,
    Praveen

    "Elisabeth Smith" <lise_bisouhotmail.com> wrote in message
    news:022301c3413e$71f6bfc0$a001280aphx.gbl...
    > Hi,
    >
    > I'm getting the following error when trying to fire a
    > trigger to a different server:
    >
    > 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].
    >
    > Original trigger definition:
    >
    >
    > CREATE trigger trg_InsertNiobeRob on dbo.tblRob
    >
    > for insert
    >
    > as
    >
    > set xact_abort on
    >
    > declare Id int
    >
    > select Id = ID
    > from inserted
    >
    > begin tran
    > insert NIOBE.MSStats.dbo.Summary
    > select * from inserted
    > where ID = ID
    > commit tran
    >
    > MSDTC is started ok. Both servres are SQL 2000
    >
    > Can anyone help?
    >
    > Thanks
    >
    > Elisabeth
    >
    >

    praveen Guest

  3. #3

    Default Re: trigger error

    Is the DTC running on both servers? Are you using Windows 2003 and if so
    have you enabled Network DTC Access?

    Pete


    "Elisabeth Smith" <lise_bisouhotmail.com> wrote in message
    news:022301c3413e$71f6bfc0$a001280aphx.gbl...
    > Hi,
    >
    > I'm getting the following error when trying to fire a
    > trigger to a different server:
    >
    > 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].
    >
    > Original trigger definition:
    >
    >
    > CREATE trigger trg_InsertNiobeRob on dbo.tblRob
    >
    > for insert
    >
    > as
    >
    > set xact_abort on
    >
    > declare Id int
    >
    > select Id = ID
    > from inserted
    >
    > begin tran
    > insert NIOBE.MSStats.dbo.Summary
    > select * from inserted
    > where ID = ID
    > commit tran
    >
    > MSDTC is started ok. Both servres are SQL 2000
    >
    > Can anyone help?
    >
    > Thanks
    >
    > Elisabeth
    >
    >

    Pete Guest

  4. #4

    Default That trigger

    Well, I have tried to check the Network DTC Access by
    going to Admin Tool/ Component Services / Computers / My
    Computer but there seems to be a problem on the source
    server I can't actually access 'My Computer'; it returns
    an error message along the lines of 'class does not exist'.

    So I guess that's where the problem lies. Our network guys
    are on the case.

    Thanks for your help all.

    Elisabeth

    >-----Original Message-----
    >Is the DTC running on both servers? Are you using Windows
    2003 and if so
    >have you enabled Network DTC Access?
    >
    >Pete
    >
    >
    >"Elisabeth Smith" <lise_bisouhotmail.com> wrote in
    message
    >news:022301c3413e$71f6bfc0$a001280aphx.gbl...
    >> Hi,
    >>
    >> I'm getting the following error when trying to fire a
    >> trigger to a different server:
    >>
    >> 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].
    >>
    >> Original trigger definition:
    >>
    >>
    >> CREATE trigger trg_InsertNiobeRob on dbo.tblRob
    >>
    >> for insert
    >>
    >> as
    >>
    >> set xact_abort on
    >>
    >> declare Id int
    >>
    >> select Id = ID
    >> from inserted
    >>
    >> begin tran
    >> insert NIOBE.MSStats.dbo.Summary
    >> select * from inserted
    >> where ID = ID
    >> commit tran
    >>
    >> MSDTC is started ok. Both servres are SQL 2000
    >>
    >> Can anyone help?
    >>
    >> Thanks
    >>
    >> Elisabeth
    >>
    >>
    >
    >
    >.
    >
    Elisabeth Smith Guest

  5. #5

    Default Re: That trigger

    That doesn't sound good. Good luck.

    Pete

    "Elisabeth Smith" <lise_bisouhotmail.com> wrote in message
    news:040901c3421d$8e71bfd0$a001280aphx.gbl...
    > Well, I have tried to check the Network DTC Access by
    > going to Admin Tool/ Component Services / Computers / My
    > Computer but there seems to be a problem on the source
    > server I can't actually access 'My Computer'; it returns
    > an error message along the lines of 'class does not exist'.
    >
    > So I guess that's where the problem lies. Our network guys
    > are on the case.
    >
    > Thanks for your help all.
    >
    > Elisabeth
    >
    >
    > >-----Original Message-----
    > >Is the DTC running on both servers? Are you using Windows
    > 2003 and if so
    > >have you enabled Network DTC Access?
    > >
    > >Pete
    > >
    > >
    > >"Elisabeth Smith" <lise_bisouhotmail.com> wrote in
    > message
    > >news:022301c3413e$71f6bfc0$a001280aphx.gbl...
    > >> Hi,
    > >>
    > >> I'm getting the following error when trying to fire a
    > >> trigger to a different server:
    > >>
    > >> 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].
    > >>
    > >> Original trigger definition:
    > >>
    > >>
    > >> CREATE trigger trg_InsertNiobeRob on dbo.tblRob
    > >>
    > >> for insert
    > >>
    > >> as
    > >>
    > >> set xact_abort on
    > >>
    > >> declare Id int
    > >>
    > >> select Id = ID
    > >> from inserted
    > >>
    > >> begin tran
    > >> insert NIOBE.MSStats.dbo.Summary
    > >> select * from inserted
    > >> where ID = ID
    > >> commit tran
    > >>
    > >> MSDTC is started ok. Both servres are SQL 2000
    > >>
    > >> Can anyone help?
    > >>
    > >> Thanks
    > >>
    > >> Elisabeth
    > >>
    > >>
    > >
    > >
    > >.
    > >

    Pete Guest

Similar Threads

  1. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  2. pl/pgsql trigger: syntax error at or near "ELSEIF"
    By Roman Neuhauser in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 14th, 04:24 PM
  3. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  4. note 33601 deleted from function.trigger-error by alindeman
    By alindeman@php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 06:11 PM
  5. note 33601 added to function.trigger-error
    By php-general@lists.php.net in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 05:57 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