Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Angry Unanswered: Activating Nested Trigger with DTC

    Hello,

    I built a trigger that activates another trigger (Nested Trigger).

    The First trigger which is located on table A inserts data into table B and in return the second trigger which is located on table B updates data in table A.

    The whole process works fine when both tables are on the same server but when the tables are on different servers i get the following error:

    "[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB Provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.]"

    Any ideas?
    kigel

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Under the server properties goto connection tab and check option for 'Enforce Distributed Transactions (DTS)", make sure to uncheck the option.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2003
    Posts
    11

    Question Still doesn't work

    I tried it and it didn't work - I even got the same error message.

    Any more ideas?
    kigel

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Make sure to check whether MSDTC is running on all involved servers.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Mar 2003
    Posts
    11
    I checked the servers and MS-DTC runs on both of them.

    Could it be connected to the settings of MS-DTC? something in the settings of MS-DTC that doesn't allow a trigger to activate another trigger?
    kigel

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    It always helps try to refresh the services and MSDTC to get it worked.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Mar 2003
    Posts
    11
    no, sorry, still doesn't help

    I stopped and played MS-DTC on both servers + refreshed services but still no good
    kigel

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I'm out of ideas now in this regard, BTW what is the SP Level?
    You may refer the case of MS Support for a fix or search under KB for any fix.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Mar 2003
    Posts
    11
    Both servers are running MS-SQL 2K, SP3



    Thanks for your time,

    I'll update on any solution, if will be founded..
    kigel

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    For information refer thru:
    run SP_SERVEROPTION from QA and deliver the result.

    Try to implement :


    SET ANSI_DEFAULTS ON
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET XACT_ABORT ON
    ..... before the transaction begins....
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Mar 2003
    Posts
    11
    all parameters were already set as mentioned.

    sp_serveroption enables changing several server parameters, which one did u mean?
    kigel

Posting Permissions

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