title: Trigger fire on each inserted row to insert into remote Table
body: <p>I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them.
  i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET</p>
  
  <p>i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.</p>
  
  <p>the problem is when i call the stored procedure from trigger, i get an error message.</p>
  
  <p>can anyone help me please to solve this issue</p>
  
  <h1>Stored Procedure:</h1>
  
  <pre><code>USE [DB1]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  alter PROCEDURE [dbo].[InsertIntoRemoteTable]
          @Description nvarchar(50)
          AS
  
          insert into 
                      OPENROWSET(
                      'SQLNCLI', 'Server=Server2;UID=MySRV2user;PWD=MySRV2Password',
                      'SELECT Description FROM [RemoteDB].[dbo].[Table_1]')
          SELECT @Description 
  </code></pre>
  
  <h1>Trigger:</h1>
  
  <pre><code>    ALTER TRIGGER [dbo].[InsertTable1]
     ON  [DB1].[dbo].[Table_1] 
     for insert
  AS 
  
  BEGIN
      SET NOCOUNT ON;
      DECLARE @Desc nvarchar(50)
      Select @Desc = i.Descr from INSERTED i;
      EXEC   InsertIntoRemoteTable @Desc
  END
  </code></pre>
  
  <h1>When i try to insert a new description value in the table i got the following error message:</h1>
  
  <pre><code>"No row was updated
  the data in row 1 was not committed
  Error source .Net SqlClient Data provider.
  Error Message: the operation could not be performed because OLE DB
  provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
  
  correct the errors entry or press ESC to cancel the change(s).
  </code></pre>
  
  <p>can anyone help please on this 
  thanks</p>