Testing linked server conccetion inside trigger or procedure

Answer 1

don't write to the remote server in the trigger.

• create a local table to store rows that need to be pushed to the remote server
• insert into this new local table in the trigger
• create a job that runs every N minutes to insert from this local table into remote server.

this job can run a procedure that can test for the connection, and when it is back up, it will handle all rows in the new local table. It can process the rows in the local table this way:

declare @OutputTable table (RowID int not null)

insert into my_linked_server.remote_database.dbo.myTable remoteTable(...columns...)
    OUTPUT INSERTED.RowID
    INTO @OutputTable
    SELECT ...columns...
        from NewLocalTable

delete NewLocalTable
   from NewLocalTable           n
       inner join @OutputTable  o ON n.RowID=o.RowID

EDIT based OP comment
after inserting into this new local table start the job from the trigger (sp_start_job), it will run in its own scope. If you can't use sql server jobs, use xp_cmdshell to execute the stored procedure (lookup SQLCMD or ISQL or OSQL, I'm not sure what you have). still schedule the job every N minutes, so it will eventually run when the connection comes up.

Answer 2

Is at least one of the servers Workgroup edition or higher? You can use Service Broker to ship your records instead of linked servers, but it will not work between to Express editions due to licensing restrictions. Is a solution relying exclusively on SQL, offers reliability in case of incidents (one of the servers is unavailable) and your updates will propagate in real time (as soon as they are committed). My site has many examples on how to do this, you can start with this article here on how to achieve high message throughput.