Trigger Between Linked Server - DTC Issue

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • niradjoshi
    New Member
    • Jul 2006
    • 2
    #1

    Trigger Between Linked Server - DTC Issue

    I made two SQL Server 2000 as linked server using same remote login

    These both server are running on different Domain

    Configuration Detail

    1. Server A - MS SQL Server 2000 SP4, windows 2003 standard edition SP1
    2. Server B - MS SQL Server 2000 SP3, windows 2003 standard edition

    I have Created same login in both the servers called test

    I have written below trigger on Server A Database

    CREATE TRIGGER [insert] ON [dbo].[ITSM_PRIORITY_T ABLE]
    FOR INSERT
    AS

    begin

    set xact_abort on
    SET NOCOUNT ON
    IF SUSER_SNAME() <> 'SDOARF'

    insert into test.OARF.dbo.A pplication (ApplicationNam e) values
    ('test1221')


    end

    But when i am inserting any record in ITSM_PRIORITY_T ABLE, it is giving me below error


    [microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New Transactio cannot enlistin the specified trasaction coordinator. ]

    [microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [ OLE/DB Provider 'SQLOLEDB' ITransactionJoi JoinTransaction returned 0x8004d00a].

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

    Workaround:

    1. I checkd DTS Service is running on both the servers
    2. Net Access is given in both servers
    3. I tried to use stored procedure which is called by insert trigger but won't work
    4. I am able to insert between these two server using four part query

    e.g

    insert into test.OARF.dbo.A pplication (ApplicationNam e) values ('test1221')

    Please guide me as soon as possible if someone faced the same issue

    This issue has really screwed up my head

    Thank you
    Tags: None
    • niradjoshi
      New Member
      • Jul 2006
      • 2
      #2
      Hi Friends,

      My issue solved.

      These are the steps followed by me.

      1. Created Linked Server with the Server Type as ‘SQL Server’

      A. provided the following in the security tab

      i. Local Login
      ii. Remote login
      iii. Remote Password
      iv. Selected Be made using this security context option and
      provided Remote Login & Remote Password

      B. Provided the following in the server options

      i. Checked data access
      ii. Checked RPC In
      iii. Checked RPC Out

      2. Checked “Enforce Distributed Transaction” in the source server
      under Server Properties -> Connections

      3. Started Distributed Transaction Coordinator is both the servers
      Enabled Network DTC Access under Control Panel -> Adminstrative
      Tools -> Component Services -> Computers -> My Computer. Right
      click My Computer and go to properties, Go to MSDTC. Click on
      Security configuration. Check Network DTC Access, Allow Inbound
      and Allow Outbound in both the source and target and restarted
      both the server (I mean the server and not sql server)

      4. Then executed the query. Before I executed the query, I did SET
      XACT_ABORT ON

      Thanks & Regards,
      Nirad

        Comment

        • sirbadchild
          New Member
          • Jul 2007
          • 1
          #3
          thank you a lot!

          This solution for the trigger is working. You were only missing one thing to enable network DTC access properly within your step 3.


          Under Authentication, you cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:
          • Incoming Caller Authentication Required
          • No Authentication Required

          Error - Office.com
          http://support.microsoft.com/default.aspx?scid=kb;en-us;817064

            Comment

            Previous template Next
            Working...