Results 1 to 7 of 7

Thread: linked server

  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: linked server

    I use a trigger which I update a linked server and this is working very good in SQL 2000. But after I migrate the database to SQL 2005. The trigger is not working with linked server any more. I remember I need to start the distributed transaction coordinator in service manager. But I couldn't find anything similar to this in SQL 2005. Is it somewhere I need to start? I got an error message like following:

    Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804

    The operation could not be performed because OLE DB provider "SQLNCLI" for
    linked server "10.2.0.119" was unable to begin a distributed transaction.

    Please help. Thanks.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Try this. our db guy had a hell of a time figuring this out

    Log into the machine named in the error message via termserv.
    Start, Run, type 'dcomcnfg.exe'

    This brings up the security manager.

    Click Component Services/Computers/My Computer folder

    Right click My Computer and select properties.

    Pick the MSDTC tab.

    Click the Security Configuration Button

    Click Network DTC Access checkbox.

    Click 'Allow Inbound', 'Allow Outbound' checkboxes.

  3. #3
    Join Date
    Nov 2006
    Posts
    43
    Thank. I tried it and unfortunately it still doesn't work.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Nov 2006
    Posts
    43
    Sorry. I have read the articles. But it doesn't help. The problem is not fixed.

  6. #6
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Have you checked Linked Server Properties?
    There are Server Options
    If you switch:
    RPC = true
    RPC Out = true
    on both servers, you should have not any problems.

    TSQL equivalent of above is:

    Code:
    USE [master]
    GO
    EXEC master.dbo.sp_serveroption @server=N'MyServerName', @optname=N'rpc', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'MyServerName', @optname=N'rpc out', @optvalue=N'true'
    GO
    Regards
    Kris Zywczyk

  7. #7
    Join Date
    Nov 2006
    Posts
    43
    Sorry. Tried it but not working.

Posting Permissions

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