Professional Web Applications Themes

trigger problem!!!!!! HELP!!!!! - Microsoft SQL / MS SQL Server

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

  1. #1

    Default trigger problem!!!!!! HELP!!!!!


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



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



    Mike Guest

  2. #2

    Default trigger problem!!!!!! HELP!!!!!

    Make sure you have your "distributed transaction
    coordinator" running. You can find it in Enterprise
    Manager under Support Services.
     
    get 
    the trigger 
    (Linked server) 
    RO.manager_num = 
    SERVERA.wrback.dbo.managers where manager_num 
    administrator/sa account, I 
    instead of trigger, 
    there is something 
    administrator Tool, 
    going wrong? 
    managers_tr_iud, Line 7 
    provider 'SQLOLEDB' 
    enlist in the 
    sql 2000 server 
    Fred Guest

  3. #3

    Default trigger problem!!!!!! HELP!!!!!

    Mike:
    Make sure the Distributed Transaction Coordinator service
    is running on both machines.

    HTH
    Vern
     
    get 
    the trigger 
    (Linked server) 
    RO.manager_num = 
    SERVERA.wrback.dbo.managers where manager_num 
    administrator/sa account, I 
    instead of trigger, 
    there is something 
    administrator Tool, 
    going wrong? 
    managers_tr_iud, Line 7 
    provider 'SQLOLEDB' 
    enlist in the 
    sql 2000 server 
    Vern Guest

  4. #4

    Default Re: trigger problem!!!!!! HELP!!!!!

    Adding to the MSDTC service running also check the trust between the two servers if they exist in tow different domains.

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    http://www.extremeexperts.com/



    "Mike" <com> wrote in message news:phx.gbl... 
    Vinodk Guest

  5. #5

    Default Re: trigger problem!!!!!! HELP!!!!!

    But they're in same domain.

    "Vinodk" <com> wrote in message
    news:%phx.gbl...
    Adding to the MSDTC service running also check the trust between the two
    servers if they exist in tow different domains.

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    http://www.extremeexperts.com/



    "Mike" <com> wrote in message
    news:phx.gbl... 
    manager_num 
    trigger, 
    something 
    'SQLOLEDB' 


    Mike Guest

Similar Threads

  1. Problem creating trigger-function with arguments (8.0rc4)
    By Michael Fuhr in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 7th, 11:08 PM
  2. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  3. Update trigger problem
    By Stephen F Zelonis in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:52 PM
  4. SQL2K Trigger problem
    By Joe Bertolini in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 2nd, 08:42 AM

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