|
|
Posted by gshawn3 on 06/22/06 18:37
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
[Back to original message]
|