473,735 Members | 2,057 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL to Oracle update trigger fails due to distributed transaction error 7391

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
successfully using both four-part naming and OPENQUERY.

Here is the actual trigger that I created:

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO

CREATE TRIGGER [MyTrigger]
ON [Person].[Contact]
AFTER UPDATE
AS

BEGIN DISTRIBUTED TRANSACTION
SELECT * from oradb..schema.t able
WHERE username = 'user'
COMMIT TRAN

However, when I update a row in AdventureWorks. Person.Contact, there is
a lag of about 2 seconds, and then I receive an error 7391 with the
following message: "The operation could not be performed because OLE DB
provider 'MSDAORA' for linked server 'oradb' was unable to begin a
distributed transaction."

Now, when I remove the "BEGIN DISTRIBUTED TRANSACTION" and "COMMIT
TRAN" from the trigger, I can update the row without any delay or error
message.

(Don't pay attention to the fact that the triggered action is a SELECT
statement. It also fails with an UPDATE statement, whether or not I use
"BEGIN DISTRIBUTED TRANSACTION." I thought using the SELECT statement
illustrates the problem more clearly.)

If I replace the triggered code with an update to a SQL Server database
on that same server (even using "BEGIN DISTRIBUTED TRANSACTION"), then
it works correctly. This leads me to believe that MS DTC is configured
properly on both machines. There is no firewall between the two
machines, and I can Telnet from the desktop to the database server on
port 135.

I have gone through many MSKB articles (280106, 839279, 329332, 259959,
193893, "Troubleshootin g Oracle Publishers" from BOL), and archived
newsgroup posts but have been unable to find any resolution for this
problem. I would appreciate any assistance you may provide.

Best regards,
George

Jun 22 '06 #1
0 3017

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
13910
by: Prashanth | last post by:
Hi, We are facing some issues in COM+ deployment. We get system error "Unable to enlist in a distributed transaction" . It has a very inconsistant behaviour. On refreshing the screens couple of times, the error disappears.Also it reappears abruptly giving an impression that the transaction failed although the changes are commited to the database.Please let me know if any of you have faced it before. This is a .Net Application with...
2
11733
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):...
4
3986
by: Abram Friesen | last post by:
Hi, I'm a developer for a software application vendor, and our application makes use of a customer-maintained Oracle 8i/9i database. We've had a customer request to support DB2 database, and I'm looking into the feasibility of migration. Our application runs on NT Server and, the DB2 database would be in some cases on a separate AIX machine, in other cases on an AS/400. There are two problems we are running into, both relating to our...
7
24684
by: Altemir | last post by:
I want to create an UPDATE statement that updates a value in a table in database A based on the results of an inner join between tables each residing in deifferent databases. What is the correct syntax for doing this? The following should give you an idea of what I'm trying to do: UPDATE A.dbo.tblCars SET A.dbo.tblCars.Car = 'Ferrari'
1
6787
by: ammar_fake | last post by:
Hello! I have an Oracle linked server connected through MSDAORA. Linked server queries work perfectly - the "openquery" ones as well as the 4-part-named ones. The problem I have is with embedding the queries within SQL Server triggers. Trigger: CREATE TRIGGER tgTest ON .
1
3291
by: zabihi | last post by:
Hi, I have set up a linked server between sql 2000 and oracle9i database using MSDAORA. Linked server works fine for selecting data from oracle database . I want to save a query in Sql server that includes a table from oracle9i and a table from local sql server, I am getting this error : Server: Msg 7391, Level 16, State 1, Procedure tr_banister, Line 16 The operation could not be performed because the OLE DB provider 'MSDAORA' was...
19
4035
by: Gregor =?UTF-8?B?S292YcSN?= | last post by:
Hi! I'm using DB2 database on one server and ORACLE database on the second one. I want to move some data from ORACLE database to DB2 database and update some columns on ORACLE database so I know what rows were transfered. I'd like to do this in a distributed XA transaction and Java. :) How is this possible ? Best regards, Kovi
16
3511
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
2
14243
by: Vinod Sadanandan | last post by:
All, Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features . Precompilers: Additional Array INSERT and SELECT Syntax Support by Pro*C/C++ and Pro*COBOL Precompilers: Dynamic SQL Statement Caching in Pro*C/C++ and Pro*COBOL Precompilers: Fix Execution Plan in Pro*C/C++ and Pro*COBOL Precompilers: Flexible B Area Length...
0
8964
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
9327
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...
1
9251
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9200
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...
1
6747
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
4562
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...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3274
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
2190
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.