473,835 Members | 2,322 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger Between Linked Server - DTC Issue

2 New Member
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_T ABLE]
FOR INSERT
AS

begin

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

insert into test.OARF.dbo.A pplication (ApplicationNam e) values
('test1221')


end

But when i am inserting any record in ITSM_PRIORITY_T ABLE, 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' ITransactionJoi JoinTransaction 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.OARF.dbo.A pplication (ApplicationNam e) 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
Jul 19 '06 #1
2 24763
niradjoshi
2 New Member
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
Jul 20 '06 #2
sirbadchild
1 New Member
thank you a lot!

This solution for the trigger is working. You were only missing one thing to enable network DTC access properly within your step 3.


Under Authentication, you cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:
• Incoming Caller Authentication Required
• No Authentication Required

http://support.microsoft.com/default...b;en-us;817064
Jul 12 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
4315
by: simonfunston | last post by:
Hi there, I'm pretty new to SQL and am having some porblems with a linked server. I have a table on a SQL server which stores employee information. I also have a view on a linked server which stores the same information. What I would like to happen is, whenever the view changes on the linked server I want the information to be changed in the table on my server. I've been trying to write a trigger to do this, but have had no
4
6302
by: Steve Bishop | last post by:
I have an Insert, Update and delete trigger on a table that works fine. It records the changes into another table called tblTracking. tblTracking records the changes so they can be uploaded to another server off site. The problem is that it fires each time my DTS package fires (every 15 minutes). The DTS package imports data from an ODBC database then compares what is in the source/destination table with a Insert Into, select not in (for...
2
11739
by: Elvira Zeinalova | last post by:
Hei, We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated machines). I am triing to connect them så that when one row is added to the table in the database in main server - then the same row is added to the same table in the second server database. I made the insert trigger on the table in the first server ( the second server is added as a linked server):...
3
1581
by: hitendra15 | last post by:
Can it be possible to create trigger/procedure in following case 2 server server A and server B A has db1 db1 has tblA B has db2 db2 has tblB can it be possible to create trigger on server A.db1.tblA as
6
3626
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that does this: 1. When a new record in a table is added, I would like the selected fields to be copied to another table (call it Master). 2. If that Master table already contains a record where the CustName = CustName, then simply ignore. If CustName does not match anything, then add the record to...
0
3022
by: gshawn3 | last post by:
Hi, I am having a hard time creating a Trigger to update an Oracle database. I am using a SQL Server 2005 Express database on a Win XP Pro SP2 desktop, linked to an Oracle 10g database on a remote Windows 2003 server. Both machines are on the same domain and very close physically (<1ms ping). I have set up the Oracle linked server in SQLEXPRESS, added the login/pw information, and I can execute select and update queries
2
1548
by: rdraider | last post by:
I created and successfully tested a trigger on a test database. Now that I want to put this on a production system, the create trigger statement takes way too long to complete. I cancelled after a few minutes. The test trigger took just a second to create. The test and production databases are identical in design. Only difference is that there are users in the production system. Any ideas? Thanks
1
6556
by: Stout | last post by:
Is it possible to create a trigger in one database, that after an insert, will update a database on a different server? If so, how would I do this? Thanks. Bill
2
5840
by: HotFrost | last post by:
Hello everyone, i am trying to work with linked servers... The local server is the one used by UPS worldwide software (it is Microsoft SQL Server Desktop Engine, v 8.00.2050). The linked server is 2005 MS server. on update/insert event for some table i hooked up the trigger that connects to linked server and updates the record in some DB. To test, i create a stored procedure that updates the record in UPS table that has that trigger....
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10815
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10526
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10237
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9348
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7770
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5640
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4435
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3094
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.