sql-server - sp_addlinkedserver 使用触发器

Question

我有以下触发器，它在运行时会导致错误:

CREATE TRIGGER ...
ON ...
FOR INSERT, UPDATE
AS   

IF UPDATE(STATUS)
BEGIN

    DECLARE @newPrice VARCHAR(50)
    DECLARE @FILENAME VARCHAR(50)
    DECLARE @server VARCHAR(50)
    DECLARE @provider VARCHAR(50)
    DECLARE @datasrc VARCHAR(50)
    DECLARE @location VARCHAR(50)
    DECLARE @provstr VARCHAR(50)
    DECLARE @catalog VARCHAR(50)
    DECLARE @DBNAME VARCHAR(50)

    SET @server=xx
    SET @provider=xx
    SET @datasrc=xx
    SET @provstr='DRIVER={SQL Server};SERVER=xxxxxxxx;UID=xx;PWD=xx;'
    SET @DBNAME='[xx]'

    SET @newPrice = (SELECT STATUS FROM Inserted)
    SET @FILENAME = (SELECT INPUT_XML_FILE_NAME FROM Inserted)

    IF @newPrice = 'FAIL'     
    BEGIN
        EXEC master.dbo.sp_addlinkedserver
            @server, '', @provider, @datasrc, @provstr

        EXEC master.dbo.sp_addlinkedsrvlogin @server, 'true'

        INSERT INTO [@server].[@DBNAME].[dbo].[maildetails]
        (
            'to', 'cc', 'from', 'subject', 'body', 'status',
            'Attachment', 'APPLICATION', 'ID', 'Timestamp', 'AttachmentName'
        )
        VALUES
        (
            'P23741', '', '', 'XMLFAILED', @FILENAME, '4',
            '', '8', '', GETDATE(), ''
        )

        EXEC sp_dropserver @server
    END

END

错误是:

Msg 15002, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 28 The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction. Msg 15002, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 17 The procedure 'sys.sp_addlinkedsrvlogin' cannot be executed within a transaction. Msg 15002, Level 16, State 1, Procedure sp_dropserver, Line 12 The procedure 'sys.sp_dropserver' cannot be executed within a transaction.

如何防止发生此错误？

Answer 1

正如错误明确指出的那样，您无法在事务中添加链接服务器。触发器作为隐式事务运行，因此您可以 ROLLBACK如果触发器执行任何验证并且该验证失败。

我真的无法想象你为什么要这样做。除了一些非常罕见的异常(exception)，我不会在这里提及，链接服务器作为临时固定装置并不理想。只需永久添加一次链接服务器，就不会出现此问题。您还可以将其管理和安全性视为管理功能，而不是将其硬编码在某个脚本中。