在触发器或过程中测试链接服务器的构想 [英] Testing linked server conccetion inside trigger or procedure

问题描述

我编写了一个触发器，用于更新链接服务器上的本地表和类似表.

I wrote a trigger that updates local table and similar table on linked server.

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;

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

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

不幸的是，当连接断开时，我收到错误消息
"Msg 3616，第16层，状态1，第2行"
'交易注定要触发.批处理已被终止'
并且本地更新将回滚.

Unfortunately when connection is down I get error message
'Msg 3616, Level 16, State 1, Line 2'
'Transaction doomed in trigger. Batch has been aborted'
and locally made update is rolled back.

有没有办法维护此错误并保留本地更新?
请注意，我在两台运行Windows XP Pro的PC上都使用SQL Server 2005 Express Edition.

Is there a way to maintain this error and keep local updates?
Note that I'm using SQL Server 2005 Express Edition on both PCs running Windows XP Pro.

edit1:SQL Server是Express Edition
edit2:两台PC都运行Windows XP Pro，因此它们不是服务器

edit1: SQL server is Express Edition
edit2: Both PCs run Windows XP Pro so these aren't servers

推荐答案

不要在触发器中写入远程服务器.

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

• 创建本地表以存储需要推送到远程服务器的行
• 在触发器中插入此新的本地表
• 创建一个每N分钟运行一次的作业，以将该本地表插入到远程服务器中.

此作业可以运行可以测试连接的过程，当备份时，它将处理新本地表中的所有行.它可以通过以下方式处理本地表中的行:

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的OP评论
插入此新本地表后，从触发器(sp_start_job)启动作业，它将在其自己的范围内运行.如果您不能使用sql服务器作业，请使用xp_cmdshell执行存储过程(查找SQLCMD或ISQL或OSQL，我不确定您拥有什么).仍然每N分钟安排一次作业，因此最终在连接建立后便会运行.

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.

这篇关于在触发器或过程中测试链接服务器的构想的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！