sp_addlinkserver с использованием триггера

У меня есть следующий триггер, который вызывает ошибку при запуске:

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

Ошибка:

Сообщение 15002, уровень 16, состояние 1, процедура sp_MSaddserver_internal, строка 28 Процедура «sys.sp_addlinkedserver» не может быть выполнена в рамках транзакции. Сообщение 15002, уровень 16, состояние 1, процедура sp_addlinkedsrvlogin, строка 17 Процедура «sys.sp_addlinkedsrvlogin» не может быть выполнена в рамках транзакции. Сообщение 15002, уровень 16, состояние 1, процедура sp_dropserver, строка 12 Процедура «sys.sp_dropserver» не может быть выполнена в рамках транзакции.

Как я могу предотвратить появление этой ошибки?


sql-server triggers linked-server
person Nanda    schedule 31.10.2009    source источник

Ответы (3)


arrow_upward
1
arrow_downward

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

Я действительно не могу себе представить, почему вы хотите сделать это в любом случае. За несколькими очень редкими исключениями, которые я не буду упоминать здесь, связанный сервер не идеален в качестве временного приспособления. Просто добавьте связанный сервер один раз, навсегда, и тогда у вас не будет этой проблемы. Вы также сможете рассматривать его управление и безопасность как административную функцию, а не жестко кодировать ее где-то в сценарии.

person Aaronaught    schedule 23.02.2010
comment
Постоянные связанные серверы являются объектами уровня сервера. Для тех из нас, кто использует серверы совместно с другими командами разработчиков, это еще один момент координации с этими командами разработчиков, которого можно избежать. Если вы используете инструменты автоматизированной сборки, такие как NAnt, для координации развертывания изменений DDL в разных средах, вам нужно как можно больше вещей на уровне базы данных или ниже. - person jennykwan; 01.03.2010
comment
@entaroadun: обратная сторона заключается в том, что для OPENROWSET требуются как специальные разрешения на исходном сервере, так и сохраненные учетные данные для места назначения. Это, вероятно, не проблема для разработки, но многие команды могут не захотеть этого в производственной среде. - person Aaronaught; 01.03.2010

arrow_upward
1
arrow_downward

Используйте OPENROWSET, если вы хотите динамически подключаться к удаленному серверу. Я делаю это все время.

person jennykwan    schedule 01.03.2010

arrow_upward
1
arrow_downward

Ваш триггер плох во многих отношениях. Во-первых, вы не можете добавить связанный сервер в триггер. Вы должны добавить его один раз в административном порядке и больше не беспокоиться об этом.

Следующее и чрезвычайно важное, даже если связанный сервер исчез, ваш триггер будет работать только в том случае, если одна строка вставлена ​​и обновлена, и не будет работать должным образом, если произойдет вставка/обновление нескольких строк. Это первое, самое основное правило разработки триггера, никогда не предполагайте, что будет обрабатываться только одна строка. Каждый раз, когда я вижу, что используется предложение значений или значение из вставленного или удаленного набора для переменной, я знаю, что триггер неисправен и его нужно переписать. Теперь это похоже на отправку электронного письма. Вы действительно хотите отправить 1907898 электронных писем, если было обновлено столько записей или только одна? Если вам нужен только один, вам нужен способ идентифицировать все затронутые идентификаторы. Что вы действительно хотите, чтобы произошло, если кому-то нужно обновить целую кучу цен и сделать это с помощью набора обновлений, а не вручную проходить через пользовательский интерфейс по одной за 10 000 цен? И не говорите, что какая-то одна запись когда-либо будет обновлена ​​или вставлена. Рано или поздно кому-то нужно будет выполнить пакетную вставку или обновление, и ваш триггер молча приведет к тому, что произойдет что-то неправильное. Вы даже не узнаете, что он потерпел неудачу, потому что он не выдаст ошибку, он просто не будет делать то, что вам нужно. Вот так и возникают проблемы с целостностью данных, которые невозможно исправить.

Другое дело, то, как это написано, БД не изменится, поэтому вам больше не нужна переменная с удаленным материалом связанного сервера. В противном случае, как написано, вам придется перейти на динамический sql, чтобы заставить его работать правильно, и это плохая идея в триггере (или где-либо еще обычно), и, поскольку он не будет меняться, нет никаких причин для его использования.

Решение на основе набора (при условии, что вам нужна одна запись для каждого элемента, вставленного или обновленного, и при условии, что вы настроили постоянный связанный сервер):

INSERT INTO myserver].mydatabase.[dbo].[maildetails] 
        ( 
            'to', 'cc', 'from', 'subject', 'body', 'status', 
            'Attachment', 'APPLICATION', 'ID', 'Timestamp', 'AttachmentName' 
SELECT   'P23741', '', '', 'XMLFAILED', INPUT_XML_FILE_NAME , '4', 
            '', '8', '', GETDATE(), '' 
FROM inserted
WHERE status = 'Fail'

Мое последнее предупреждение вам, что даже это не удастся, если связанный сервер по какой-либо причине выйдет из строя. Это означает, что пока он не работает, никакие записи не могут быть добавлены или изменены в таблице. Подумайте об этом очень внимательно, прежде чем помещать это в триггер.

person HLGEM    schedule 01.03.2010