473,371 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,371 software developers and data experts.

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_IDENTIFIER ON
GO

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

BEGIN DISTRIBUTED TRANSACTION
SELECT * from oradb..schema.table
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, "Troubleshooting 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 2984

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

Similar topics

7
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...
2
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 - ...
4
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...
7
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...
1
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...
1
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...
19
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...
16
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...
2
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:...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.