SQLNCLI Error For Linked server

Question

SQLNCLI Error For Linked server

nikhil.desai1

SSCommitted

Points: 1948
More actions
January 15, 2012 at 10:32 pm

#266696

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.
Please give me some solution for the same.
Thanks and Regrards,
Nikhil P Desai

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

nikhil.desai1 (1/15/2012)
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.
Please give me some solution for the same.
Thanks and Regrards,
Nikhil P Desai

Is DTC service running? If no start it. It my solve your problem.

nikhil.desai1 SSCommitted Points: 1948 More actions · Answer 2

DTC is started in services.msc on both server A and B

Also in Component Services - Computer - My Computer - Properties - MSDTC - Security Configuration - following setting are checked on server A and B

1 Network DTC

2 Allow remote Client

3 Allow remote Administration

4. Allow inbound and outbound

5. No Authentication is required.

6. Enable Transaction Internet Protocol

7. Enable XA transactions.

But still the error is not resolved

Thanks and Regards,

Nikhil Desai

nikhil.desai1 SSCommitted Points: 1948 More actions · Answer 3

DTC is started in services.msc on both server A and B

Also in Component Services - Computer - My Computer - Properties - MSDTC - Security Configuration - following setting are checked on server A and B

1 Network DTC

2 Allow remote Client

3 Allow remote Administration

4. Allow inbound and outbound

5. No Authentication is required.

6. Enable Transaction Internet Protocol

7. Enable XA transactions.

But still the error is not resolved

Thanks and Regards,

Nikhil P Desai

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

If you execute the following on SSMS query windows (not inside trigger) on Server B, does it work?

EXEC [ipaddress].msdb.dbo.sp_send_dbmail

@profile_name='SQLDBA',

@recipients = 'abc@xyz.com',

@body = 'Test Mail,

@subject = 'Testing Trigger ;

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

Have you been able to perform a manual distributed transaction between servers?

Such as

begin distributed transaction

select * from [ipaddress].master.sys.databases

commit distributed transaction

If not you have a DTC issue that needs to be resolved. This could be a setting for your linked server or a firewall problem.

Shamless self promotion - read my blog http://sirsql.net