Post reply

Updating Triggers

  • March 26, 2005 at 3:07 am

    #92783

    Little help needed,

    Currently have server1 and db1 that have triggers built on them that push data to server2 db2. Everything works fine here, but went through server/db migration and i have updated the triggers in server1 db1 to now reference the new server3. db3.  Everytime i try to insert a record in db1 it hangs up, its stuck in some a loop. if i switch the reference back to server2 db2 works fine again.  linked server has been added to sysserver. All accounts have been setup on new server.

    FYI

    1&2 = SQL Server 2000 on W2K server

    3=SQL Server 2000 on W2003 Server

    Thanks

     

     

     

  • March 29, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • March 29, 2005 at 7:27 pm

    #549454

    A manual insert from d1 into d3, capture the error.

  • March 29, 2005 at 8:25 pm

    #549457

    I believe the difference you are seeing is due to DTC security settings in Windows 2003.  When my company upgraded to I Win 2003 ran into a similar problem.  Triggers implicitly are in a transaction with their firing action, and given the cross-server statements, the statement is using a distributed transaction.  Windows 2003 modified the security settings for these (see:  http://support.microsoft.com/default.aspx?scid=kb;en-us;817064)

    You (or a Windows admin) should navigate to the following:

    • Start / Program Files / Administrative Tools / Component Services (or follow the path in the KB article above)
    • Expand the Component Services node until you reach "My Computer"
    • Right-click on My Computer and select Properties
    • Go to the MSDTC tab and select "Security Configuration"

    Verify that "Network DTC Access" is selected.  You may also need to select "Network Administration", "Network Transactions", and "Network Clients". 

    Also verify that the DTC Logon Account has network privileges.  The account NT AUTHORITY\NetworkService has priviliges; LocalSystem does not.

    At this point, stop and start MSDTC.  I think you also need to stop/start SQL Server service as well. 

    Hope this helps,

    Scott Thornburg

  • March 30, 2005 at 11:13 pm

    #549716

    I got it to work yesterday.

    Scott, I wish you had replied a little earlier but I appreciate your feedback and you are absolutely right.   In Windows 2003 lot of setting by default are disabled.   The challenge for me was that i wasn't able to generate an error code it just kept hanging. But, once i tried to link the new server that has 2003 with another server with the same OS then i was able to genereate the error code which led me to the resolution. For some reason i wasn't able to genereate the error code with having windows 2000 on one box linked to a windows 2003 box. 

    This was good find becuase we are going through a server migration and it will be useful.

    Thanks again

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply