|
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:
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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?
(comments are locked)
|
|
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.. 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 '10 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 '10 at 10:12 AM
Jack Corbett
(comments are locked)
|

