title: Testing linked server conccetion inside trigger or procedure
body: <p>I wrote a trigger that updates local table and similar table on linked server.   </p>
  
  <p><code>
  CREATE TRIGGER myTtableUpdate ON myTable<br>
  AFTER UPDATE
  AS<br>
  IF (COLUMNS_UPDATED() > 0)<br>
  BEGIN<br>
    DECLARE @retval int;<br>
    BEGIN TRY<br>
      EXEC @retval = sys.sp_testlinkedserver N'my_linked_server';<br>
    END TRY<br>
    BEGIN CATCH<br>
      SET @retval = sign(@@error);<br>
    END CATCH;  </p>
  
  <p>IF (@retval = 0)<br>
    BEGIN<br>
      UPDATE remoteTable SET remoteTable.datafield = i.datafield<br>
        FROM my_linked_server.remote_database.dbo.myTable remoteTable<br>
        INNER JOIN inserted i ON (remoteTable.id = i.id)<br>
    END<br>
  END  -- end of trigger
  </code></p>
  
  <p>Unfortunately when connection is down I get error message<br>
  <em>'Msg 3616, Level 16, State 1, Line 2'</em><br>
  <em>'Transaction doomed in trigger. Batch has been aborted'</em><br>
  and locally made update is rolled back.   </p>
  
  <p>Is there a way to maintain this error and keep local updates?<br>
  Note that I'm using SQL Server 2005 Express Edition on both PCs running Windows XP Pro.</p>
  
  <p>edit1: SQL server is Express Edition<br>
  edit2: Both PCs run Windows XP Pro so these aren't servers</p>