January 14, 2012 at 10:31 pm
Hi,
I have created DDL trigger On Server A and Server B
On server A
Use [DBName]
GO
CREATE TRIGGER [ddltrg_Audit_LOG] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLDBA',
@recipients = 'abc@xyz.com',
@body = 'Test Mail,
@subject = 'Testing Trigger ;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddltrg_Audit_LOG] ON DATABASE
-----------------------------------------------------------
On server A whenever any user does ddl events mails are received.
On Server B i have created Linked Server to A using ipaddress and Created DDL Trigeer as
follows.
On Server B
Use [DBName]
GO
CREATE TRIGGER [ddltrg_Audit_LOG] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
EXEC [ipaddress].msdb.dbo.sp_send_dbmail
@profile_name='SQLDBA',
@recipients = 'abc@xyz.com',
@body = 'Test Mail,
@subject = 'Testing Trigger ;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddltrg_Audit_LOG] ON DATABASE
-----------------------------------------------------------
When ddl level events occurs on server B I getting following error in Query Analyzer
OLE DB provider "SQLNCLI" for linked server "ipaddress" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure ddltrg_Audit_LOG, Line 89
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ipaddress" was unable to begin a distributed transaction.
Thanks and Regrards,
Nikhil P Desai
January 16, 2012 at 7:42 am
Maybe your MSDTC service is firewalled between the two servers?
Can you do any distributed transaction at all?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply