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:
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:
How can I fix this problem.
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.
answered Feb 24 '10 at 10:43 AM
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..
answered Feb 24 '10 at 11:22 AM
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?
answered Mar 03 '10 at 08:16 AM