sp_addlinkserver using trigger
- by Nanda
I have the following trigger, which causes an error when it runs:
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
The error is:
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.
How can I prevent this error from occurring?
