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

sukran dere gravatar image

sukran dere
43 2 2 2

(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

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(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

sukran dere gravatar image

sukran dere
43 2 2 2

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

sukran dere gravatar image

sukran dere
43 2 2 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x120
x108

asked: Feb 24, 2010 at 10:13 AM

Seen: 2318 times

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