x

Linked Server Trigger Problem

Hi All!

My problem is that I created a linked server named RETAIL in SQL SERVER 2005 for Oracle db.

I can insert, delete, select, update datas from oracle db.like this:

SELECT * FROM RETAIL..USERNAME.TABLE1;

SELECT FROM OPENQUERY(RETAIL,'SELECT FROM TABLE1');

INSERT INTO RETAIL..USERNAME. TABLE1 VALUES (...);

UPDATE RETAIL..USERNAME.TABLE1 SET FIELD1= 'DENEME ';

DELETE FROM RETAIL..USERNAME.TABLE1 WHERE..

But when I use insert, update, delete statements for ORACLE DB in SQL SERVER TRIGGER trigger is created successfully but when it begins to run gives error like this:

OLE DB provider "MSDAORA" for linked server "RETAIL" returned message "ORA-01041:internal error. hostdef extension doesn"t exist

My Trigger is this:

CREATE TRIGGER REYON ON STOK_REYONLARI AFTER INSERT AS DECLARE @RECNO INT

BEGIN

SET @RECNO =(SELECT MAX(RYN_recNO) FROM STOK_REYONLARI)

INSERT INTO RETAIL..USERNAME.TABLE1 VALUES(@RECNO, 'I', 'STOK_REYONLARI')

END

How can I fix this problem.

more ▼

asked Feb 24, 2010 at 10:13 AM in Default

avatar image

sukran dere
43 2 2 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I don't know for sure what the problem is, but I can say that accessing linked server in a trigger is a bad idea. It lengthens your original transaction and guess what happens if the trigger fails because the linked server isn't available, your original transaction fails as well.

These types of things should be done asynchronously with Service Broker or your own home-grown solution.

more ▼

answered Feb 24, 2010 at 10:43 AM

avatar image

Jack Corbett
1.1k 3 4 7

(comments are locked)
10|1200 characters needed characters left

I can do that asynchronously by using SQL SERVER Jobs. But that must work synchronously. Synchronization is essential for me. So how can I provide the Synchronization without using trigger or linked server between Oracle and Sql Server database.The tables are not same so replication is not solution for me.

Thanks a lot for your attention Jack Corbett..

more ▼

answered Feb 24, 2010 at 11:22 AM

avatar image

sukran dere
43 2 2 4

I would create a stored procedure that did both pieces and require that all changes go through the stored procedure. It is not possible in all cases due to preexisting software, company policies, etc, but it is generally the best option when it is availble.

Feb 24, 2010 at 03:21 PM TimothyAWiseman

I agree with Timothy. I'd also ask if a 1 to 2 minute delay is acceptable. If you use Service Broker you could use Activation, http://msdn.microsoft.com/en-us/library/ms171617.aspx, and have the process be almost instantaneous, but still allow the original insert to take place without needing the linked server to be available.

Feb 25, 2010 at 10:12 AM Jack Corbett
(comments are locked)
10|1200 characters needed characters left

As a final solution 1 to 2 minute delay can be acceptable for me.

I haven't use Service Broker before. Could you give more information about Service Broker, Jack Corbett?

How can I use Service Broker between oracle db and sql server db?

more ▼

answered Mar 03, 2010 at 08:16 AM

avatar image

sukran dere
43 2 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x139
x129

asked: Feb 24, 2010 at 10:13 AM

Seen: 2531 times

Last Updated: Feb 24, 2010 at 11:53 AM

Copyright 2016 Redgate Software. Privacy Policy