January 28, 2014 at 6:08 pm
Hello, I am having a hard time migrating an existing procedure we have on our production SQL Server 2008 R2 SP2. We currently do inserts into another SQL Server 2000 database from our system based on certain criteria. The db, the SQL 2000 system, is being migrated to Oracle 11g. We have been using a SQL linked server to do the inserts up till this point and would now like to use an Oracle linked server to do the inserts.
We have views set up in our database that point to the tables in the linked server. We have select, insert, update and delete privledges to the oracle 11g tables.
One problem I would like to solve when I do the switch over, is that we do not have any error handling on this procedure. We are doing multiple inserts to multiple tables in the other system and right now, if one of those inserts fails it does not have any sort of error handling so some of the inserts could work and others might fail. This would/does cause data integrity issues.
So, I want to use transactions to rollback if one of the inserts fails. I have successfully configured the new linked server and ran a test insert with just a regular insert query using our views that point to their Oracle database. It works just fine. I placed the test insert query into a TRY and CATCH block where I have the CATCH block writing to an error log table if the insert fails. This also works just fine.
My problem lies in when I try to put the insert statement into a TRANSACTION. When I do that, I get the following error message:
New transaction cannot enlist in the specified transaction coordinator.
I have scowered the web and I know I have MSDTC set up properly with the no authorization required thing but I'm really confused what my next move is.
1) Is it not possible to put linked server queries into a TRANSACTION?
2) If it is possible, what am I doing wrong or what configurations do I not have correct?
Please let me know if I have not explained anything properly... somehow, I always seem to ask the hard questions that I can never get answered. 🙂
January 29, 2014 at 6:58 am
I still haven't figured this out. 🙁
January 30, 2014 at 7:03 am
Nobody has any ideas?
January 30, 2014 at 10:04 am
Well ok, I guess neccessity is the mother of invention and I finally got this to work myself after days of frustration. 🙂
I finally figured out that in order to use DTC with an Oracle linked server I needed to install 64-bit Oracle Data Access Components (ODAC) which contains the 64-bit Oracle Services for Microsoft Transaction Server 11.2.0.3.0. This installs a running service called OracleMTSRecoveryService.
This is of course after making sure the Microsoft DTC was configured properly and running.
Also, had to change the property on my linked server for Enable Promotion of Distributed Transactions to False.
I am now able to do an insert into my oracle table from sql server using BEGIN TRANSACTION inside a TRY and CATCH block.
Just thought I would post the solution in case anyone ever searches for this. I found a lot of stuff on the web about people asking the same question as me but few with solved answers. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply