Linked Server and Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server and Trigger

Author

Topic

snarayan
Starting Member

10 Posts

Posted - 2003-11-26 : 23:13:11

Hi All

Have a trigger on a table that gets executed on insert. The trigger executes a stored procedure on a remote server via linked server. When the trigger is executed, get the following error message

Server: Msg 7391, Level 16, State 1, Procedure trg_Radius_Accounting, Line 64
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

But if I execute the remote stored procedure using Enterprise Sql Query Analizer it works example:

declare @username nvarchar (50)
declare @usage int
set @username = 'testlive2@xyz.com'
exec mediation.mediation.dbo.rad_GetVoiceNetworkUsage @username, @usage output
print @usage

Have confirmed MS DTC is running on the remote server.

Any help is appreciated

Many thanks
Sanjay

xpandre
Posting Yak Master

212 Posts

Posted - 2003-11-27 : 01:29:05

Be sure that the DTC service is enabled for network access. Depending on the OS version and the OS installation the DTC could be running but without network access.

Check reg_word key \HKLM\Software\Microsoft\msdtc\Security\NetworkDTCAccess.
If it exists and is set to 0, DTC cannot get to the network. If it doesn't exist at all then don't change anything because I haven't diagnosed theissue properly. You will not see this key on W2K and that is perfectly fine.

To enable Network DTC Access: Control Panel | Add / Remove Windows
Components | Application Server | Enable Network DTC Access - Check the box and complete. You should get past the ITransactionJoin error.

From http://www.dbforums.com
HTH

snarayan
Starting Member

10 Posts

Posted - 2003-11-30 : 20:52:46

Thanks. The problem was with the firewall. The servers are on different network and there is a firewall between them. The firewall was blocking tcp ports > 5000. So reconfigured the linked servers to use a sub set of port and opened these ports on the firewall. There is a support doc from Microsoft that describes the process of configuring DTC to work through a firewall, article number 250367.

Sanjay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources