Тестирование соответствия связанного сервера внутри триггера или процедуры

Question

Я написал триггер, который обновляет локальную таблицу и аналогичную таблицу на связанном сервере.

CREATE TRIGGER myTtableUpdate ON myTable
AFTER UPDATE AS
IF (COLUMNS_UPDATED() > 0)
BEGIN
DECLARE @retval int;
BEGIN TRY
EXEC @retval = sys.sp_testlinkedserver N'my_linked_server';
END TRY
BEGIN CATCH
SET @retval = sign(@@error);
END CATCH;

IF (@retval = 0)
BEGIN
UPDATE remoteTable SET remoteTable.datafield = i.datafield
FROM my_linked_server.remote_database.dbo.myTable remoteTable
INNER JOIN inserted i ON (remoteTable.id = i.id)
END
END -- end of trigger

К сожалению, когда соединение не работает, я получаю сообщение об ошибке
"Сообщение 3616, уровень 16, состояние 1, строка 2"
"Транзакция обречена на триггер. Пакет был прерван
и локально сделанное обновление откатывается.

Есть ли способ сохранить эту ошибку и сохранить локальные обновления?
Обратите внимание, что я использую SQL Server 2005 Express Edition на обоих компьютерах под управлением Windows XP Pro.

edit1: SQL-сервер Express Edition
edit2: оба компьютера работают под управлением Windows XP Pro, так что это не серверы

Answer 1

Не пишите на удаленный сервер в триггере.

• создать локальную таблицу для хранения строк, которые нужно отправить на удаленный сервер
• вставить в эту новую локальную таблицу в триггере
• создайте задание, которое выполняется каждые N минут для вставки из этой локальной таблицы в удаленный сервер.

это задание может запустить процедуру, которая может проверить соединение, и при его резервном копировании оно будет обрабатывать все строки в новой локальной таблице. Он может обрабатывать строки в локальной таблице следующим образом:

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

РЕДАКТИРОВАТЬ на основе OP комментарий
после вставки в эту новую локальную таблицу запустите задание из триггера (sp_start_job), оно будет выполняться в собственной области видимости. Если вы не можете использовать задания сервера SQL, используйте xp_cmdshell для выполнения хранимой процедуры (ищите SQLCMD или ISQL или OSQL, я не уверен, что у вас есть). по-прежнему планируйте задание каждые N минут, поэтому в конечном итоге оно будет выполняться при установлении соединения.

Answer 2

Это хотя бы один из серверов Workgroup Edition или выше? Вы можете использовать Service Broker для отправки своих записей вместо связанных серверов, но он не будет работать между выпусками Express из-за лицензионных ограничений. Это решение, основанное исключительно на SQL, обеспечивает надежность в случае инцидентов (один из серверов недоступен), и ваши обновления будут распространяться в режиме реального времени (как только они будут зафиксированы). На моем сайте есть много примеров того, как это сделать, вы можете начать с этой статьи здесь о том, как добиться высокой пропускной способности сообщений.