Hi,

I have a quick question about distributed transaction.

We currently use a stored procedure to insert data in two tables, one of the table is located on a SQL 2000 server and the other one on an Oracle server (Linked server).

I would like to ensure data integrity between the two server. For some reason i'm not able to retreive an error code from oracle when the transaction fail...

How could the SP Rollback the entire transaction on SQL and Oracle side?

Here is an example of the SP we use:

CREATE PROCEDURE PLB_1_sp
(@Value1 INTEGER,
@Value2 INTEGER)
AS
BEGIN

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

INSERT INTO SQL1
VALUES (@Value1,@Value2)

IF (@@ERROR <> 0) GOTO ERROR_HANDLER

INSERT INTO linkedSvr..ORACLE.Table1
VALUES (@Value1,@Value2)

IF (@@ERROR <> 0) GOTO ERROR_HANDLER

COMMIT TRANSACTION

ERROR_HANDLER:
ROLLBACK TRANSACTION
SET XACT_ABORT OFF

END

Thanks for your help