title: SQL-Server trigger update issue from over linked server
body: <p>I would like to update rows over linked server. I created linked server on my local SQL-Server with <a href="http://www.informit.com/articles/article.aspx?p=21644" rel="nofollow">this</a> instruction.</p>
  
  <p>I can update rows over Query Analyzer with below code. No error returned and rows update on Linked Server successfully. </p>
  
  <pre><code>UPDATE  [LinkedServer].[Database].[dbo].[Table]
  SET A = 'A', B = 'B'
  WHERE id = 1
  </code></pre>
  
  <p>But when I created a update trigger to my table on my local sql server like below,</p>
  
  <pre><code>ALTER TRIGGER [TriggerLocalServerTable]
     ON  dbo.[LocalServerTable]
      FOR UPDATE
  AS 
  
  DECLARE @A varchar(4)
  DECLARE @B varchar(4)
  DECLARE @id int
  
  BEGIN
   SELECT
   @A = A,
   @B = B,
   @id = id
   FROM inserted
  
   UPDATE  [LinkedServer].[Database].[dbo].[Table]
   SET A = @A, B = @B
   WHERE id = @id      
  END
  </code></pre>
  
  <p>When trigger performed, returns this error like below,</p>
  
  <blockquote>
    <p>OLE DB provider "SQLNCLI" for linked
    server "LinkedServer" returned message
    "The partner transaction manager has
    disabled its support for
    remote/network transactions.". Msg
    7391, Level 16, State 2, Procedure
    TriggerLocalServerTable, Line 45
    The operation could not be performed
    because OLE DB provider "SQLNCLI" for
    linked server "LinkedServer" was unable
    to begin a distributed transaction.</p>
  </blockquote>
  
  <p>I applied <a href="http://www.sqlwebpedia.com/content/msdtc-troubleshooting" rel="nofollow">this</a> instruction to local server for solving issue, anything changed.</p>
  
  <p>More details;</p>
  
  <ul>
  <li>if defined my linked server with its
  remote ip address.  </li>
  <li>My local server and Linked Server
  not in same domain.</li>
  <li>Linked Server Security setting is "Be
  made using this security context"</li>
  <li>Local server version is Windows
  Server 2003 SP2, SQL-Server version is 2005 
  standart.</li>
  <li>Linked server OS version is 2008 and
  SQL-Server version 2008 (64 bit).</li>
  <li>I couldn't access remote server<br>
  with RDP. i can access just from sql
  port (1433). </li>
  <li>i set linked server    "RPC" and "RPC
  Out" to "True" from    "Security Option".</li>
  </ul>
  
  <p>Do you have any idea to solve this issue.
  Thank you very much already now.</p>
  
  <p><strong>Edit:</strong> 
  I solved this issue. Firstly i created a trigger for update on [TriggerLocalServerTable]. This trigger's job is inserting new update fields to a local table. This new table using for updated temp datas. Then i created a job that runs every hour for update fileds on LinkedServer. This job gets data from temp table then update to LinkedServer table's fields.</p>
  
  <p>Regards, Kerberos.</p>