sp_addlinkserver using trigger
Posted
by Nanda
on Stack Overflow
See other posts from Stack Overflow
or by Nanda
Published on 2009-10-31T00:55:50Z
Indexed on
2010/03/22
13:51 UTC
Read the original article
Hit count: 519
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?
© Stack Overflow or respective owner