August 4, 2010 at 12:58 am
Hi All,
I want to replicate data from Sql Server to Oracle db with using linked server.
For this reason I wrote a before insert trigger that inserts data from Sql Server to Oracle.
But when trigger gets a link exception, or a connection exception, the insertion
works neither sql server nor oracle db.
But if an exception occures from Oracle in the trigger, Sql Server wouldn't be influenced for this error.
The data would be inserted to Sql server but wouldn't be inserted to Oracle.
How can it be possible?
August 4, 2010 at 7:21 am
Did you enclose the whole operation as a "distributed transaction"? ... like:
begin distributed tran
do-whatever-you-have-to-do
commit work
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 4, 2010 at 8:35 am
Yes I tried your suggestion , Paul. But there is no change. My code is like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[INSERT_BRAND]
ON [dbo].[BRANDS]
AFTER INSERT
AS
SET XACT_ABORT ON
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO OPENQUERY(LINKEDSRV,
'SELECT BRAND_NAME, INP_DATE, BRAND_CODE
FROM ORACLEDB.TABLE_BRAND')
SELECT
BRANDNAME,
REPLACE(CONVERT(NVARCHAR(11) , GETDATE(),6),' ', '-'),
BRANDCODE
FROM INSERTED
COMMIT TRAN
Sql server table name is BRANDS,
Oracle table name is TABLE_BRAND,
the BRANDS.BRANDCODE field's type is varchar but it is used as integer.like 1,2,3,08,09...etc
the TABLE_BRAND.BRAND_CODE field's type is number.
But when a string value (like 'nike') wanted to inserted to the field BRANDS.BRANDCODE;
the trigger gives exception of this :
"error converting data type nvarchar to numeric"
And then the value is inserted neither oracle nor sql server db.
In this situation I want that the data is inserted to sql server but not inserted to oracle. Sql server side musn't
effected from like this errors.
August 4, 2010 at 3:55 pm
sukrandere 74618 (8/4/2010)
In this situation I want that the data is inserted to sql server but not inserted to oracle. Sql server side musn'teffected from like this errors.
Shall we undestand you want to split the transaction meaning let it fail in Oracle side and let it complete in SQL Server side?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 5, 2010 at 12:13 am
PaulB-TheOneAndOnly (8/4/2010)
sukrandere 74618 (8/4/2010)
In this situation I want that the data is inserted to sql server but not inserted to oracle. Sql server side musn'teffected from like this errors.
Shall we undestand you want to split the transaction meaning let it fail in Oracle side and let it complete in SQL Server side?
Yes you are right Paul,
by the way sorry for my poor English.
August 5, 2010 at 1:59 pm
sukrandere 74618 (8/5/2010)
PaulB-TheOneAndOnly (8/4/2010)
sukrandere 74618 (8/4/2010)
In this situation I want that the data is inserted to sql server but not inserted to oracle. Sql server side musn'teffected from like this errors.
Shall we undestand you want to split the transaction meaning let it fail in Oracle side and let it complete in SQL Server side?
Yes you are right Paul,
by the way sorry for my poor English.
No worries. It wasn't the English, it was the concept that got me confused.
Usually we want to preserve the integrity of transactions - not the other way around.
I have never tried something like that but my guess is - if doable at all - that by taking out all references to explicit transactions it may work in a distributed environment meaning, if we do not tell SQL Server this is a distributed transaction it may actually don't care of what happens on the Oracle side.
Have you tried taking out all references to explicit transactions?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply