Sql-Server
將觸發器插入連結伺服器錯誤
我在表中創建了一個插入觸發器,該觸發器將在插入/更新/刪除時觸發。觸發器應該發送有關觸發它的查詢的資訊,但每次觸發時,都會發生以下錯誤。
OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" returned message "The transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Procedure AuditlDealerDebitAuditTrailTEST, Line 22 The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "TMP-DBA-DB" was unable to begin a distributed transaction.
這是我的觸發器查詢:
create trigger dbo.AuditlDealerDebitAuditTrailTEST on [dbo].[lDealerDebitAuditTrailTEST] after UPDATE, INSERT, DELETE as declare @TableName varchar(20), @User varchar(20), @PCNo varchar(20), @server nvarchar(30), @Database varchar(30), @table varchar(30),@activity varchar(20), @date datetime; begin set @TableName = 'lDealerDebitAuditTrailTEST' if exists(SELECT * from inserted) and exists (SELECT * from deleted) begin SET @User = SYSTEM_USER; set @PCNo = HOST_NAME(); set @server = @@SERVERNAME; set @Database = DB_NAME(); Set @table = @TableName; SET @activity = 'UPDATE'; set @date = GETDATE(); INSERT into [TMP-DBA-DB].[Josel_TestDB].[dbo].[audit](ExecutedBy, PCNo, "Server", "Database", "Table", Activity, ExecutedOn) values (@User, @PCNo, @server, @Database, @table, @Activity, @date); end If exists (Select * from inserted) and not exists(Select * from deleted) begin SET @Activity = 'INSERT'; SET @User = SYSTEM_USER; set @PCNo = HOST_NAME(); set @server = @@SERVERNAME; set @Database = DB_NAME(); Set @table = @TableName; set @date = GETDATE(); INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy, PCNo, "Server", "Database", "Table", Activity, ExecutedOn) values (@User, @PCNo, @server, @Database, @table, @Activity, @date); end If exists(select * from deleted) and not exists(Select * from inserted) begin SET @activity = 'DELETE'; SET @User = SYSTEM_USER; set @PCNo = HOST_NAME(); set @server = @@SERVERNAME; set @Database = DB_NAME(); Set @table = @TableName; set @date = GETDATE(); INSERT into [TMP-DBA-DB].[Josel_TestDB].dbo.audit(ExecutedBy, PCNo, "Server", "Database", "Table", Activity, ExecutedOn) values (@User, @PCNo, @server, @Database, @table, @Activity, @date); end end GO
您是否嘗試過設置分銷商交易協調員?
分佈式事務是需要 SQL Server 從您正在使用的 SQL Server 實例之外的源獲取數據的事務(這通常需要使用連結伺服器等。
分佈式事務的一個例子是:
INSERT MyTable (Col1, Col2, Col3....) SELECT Col1, Col2, Col3 ... FROM OPENQUERY(MyLinkedServer, 'SELECT Col1, Col2, Col3 ... FROM RemoteTable WHERE predicate1 = Condition1') INNER JOIN Table2 ON Table2.ColX = Col1
要設置分佈式事務協調器,請按照以下步驟操作:
現在您點擊應用,它會要求重新啟動服務,然後您說
yes
。這篇文章也值得一讀: