Trigger between Linked Server - DTC Issue

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 Administration (2000)

Trigger between Linked Server - DTC Issue

Author

Topic

niradjoshi
Starting Member

21 Posts

Posted - 2006-07-19 : 01:40:39

I made two SQL Server 2000 as linked server using same remote login

These both server are running on different Domain

Configuration Detail

1. Server A - MS SQL Server 2000 SP4, windows 2003 standard edition SP1
2. Server B - MS SQL Server 2000 SP3, windows 2003 standard edition

I have Created same login in both the servers called test

I have written below trigger on Server A Database

CREATE TRIGGER [insert] ON [dbo].[ITSM_PRIORITY_TABLE]
FOR INSERT
AS

begin

set xact_abort on
SET NOCOUNT ON
IF SUSER_SNAME() <> 'test'

insert into test.mydatabase.dbo.Application (ApplicationName) values
('test1221')

end

But when i am inserting any record in ITSM_PRIORITY_TABLE, it is giving me below error

[microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: New Transactio cannot enlistin the specified trasaction coordinator. ]

[microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [ OLE/DB Provider 'SQLOLEDB' ITransactionJoiJoinTransaction returned 0x8004d00a].

[microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Workaround:

1. I checkd DTS Service is running on both the servers
2. Net Access is given in both servers
3. I tried to use stored procedure which is called by insert trigger but won't work
4. I am able to insert between these two server using four part query

e.g

insert into test.mydatabase.dbo.Application (ApplicationName) values ('test1221')

Please guide me as soon as possible if someone faced the same issue

This issue has really screwed up my head

Thank you

niradjoshi
Starting Member

21 Posts

Posted - 2006-07-20 : 03:30:46

Hi Friends,

My issue solved.

These are the steps followed by me.

1. Created Linked Server with the Server Type as ‘SQL Server’

A. provided the following in the security tab

i. Local Login
ii. Remote login
iii. Remote Password
iv. Selected Be made using this security context option and
provided Remote Login & Remote Password

B. Provided the following in the server options

i. Checked data access
ii. Checked RPC In
iii. Checked RPC Out

2. Checked “Enforce Distributed Transaction” in the source server
under Server Properties -> Connections

3. Started Distributed Transaction Coordinator is both the servers
Enabled Network DTC Access under Control Panel -> Adminstrative
Tools -> Component Services -> Computers -> My Computer. Right
click My Computer and go to properties, Go to MSDTC. Click on
Security configuration. Check Network DTC Access, Allow Inbound
and Allow Outbound in both the source and target and restarted
both the server (I mean the server and not sql server)

4. Then executed the query. Before I executed the query, I did SET
XACT_ABORT ON

Thanks & Regards,
Nirad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources