Distributed Query in Trigger

Question

Distributed Query in Trigger

indrama

Valued Member

Points: 59
More actions
March 6, 2002 at 3:31 am

#78169

Hi, I’m new. Has anyone ever tried to do a distributed query in trigger? Below is the trigger code.
CREATE TRIGGER tr_DistributedQ ON dbo.Table1
FOR INSERT
AS
BEGIN
insert link1.db1.dbo.remotetable1
select '00000'
END
To test the trigger:
INSERT Table1 SELECT 'XXXXX'
It works fine when the linked server is also SQL Server (make sure that XACT_ABORT is ON), but it seems not for others. I’ve tried the Access (Jet) and Oracle databases.
The query works when I run it outside the trigger:
For Oracle:
insert ora1..schema1.remotetable1
select '00000'
For Jet:
insert access1...remotetable1
select '00000'
Any idea?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Tested and I always get an ODBC error, may just not be allowed under those circumstances, I will look for details and post if I find.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Ok found this out, for some providers you have to have Distributed Transaction Coordinator running. When I turned it on I got the error for an Access database that Jet 4 does support an interface that would allow the transaction. Do not have any other test sources to check.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

indrama Valued Member Points: 59 More actions · Answer 3

I think I know what cause it. It looks like that the Oracle OLE DB Provider doesn’t support nested transaction. Try this:

BEGIN TRAN T1

INSERT Table1 SELECT 'xxxx'

BEGIN DISTRIBUTED TRAN T2

INSERT ora1..schema1.remotetable1 SELECT '2'

COMMIT TRAN T2

COMMIT TRAN T1

Statement above will raise an error:

Server: Msg 7391, Level 16, State 1, Line 5

The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction.

Now put it in separate transaction:

INSERT Table1 SELECT 'xxxx'

INSERT ora1..schema1.remotetable1 SELECT '2'

Thanks anyway.

PS. I’m using the Oracle Provider for OLE DB that came with Oracle 8.1.6 installation. I don’t know why, but the OLE DB for Oracle that included in SQL Server installation doesn’t work at all.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Microsoft hasn't made any version changes since, I think, Oracle 7x was released, may have been earlier. I think the issues is Oracle is not willing to lincense them the code anymore.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)