Lavsa, Rich
Rich_Lavsa at pghcorning.com
Tue Oct 4 12:10:24 CDT 2005
I found the answer late yesterday. It was in fact invalid registry entries, they were still pointing to the Oracle 8i files, instead of the new 9 files. All is back in order. Just to post back a solution to keep in the archives, found the solution in KB280106. Thanks for the response Arthur Rich -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Monday, October 03, 2005 6:33 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] unable to begin a distributed transaction I may have an inkling as to your problem. When updating, the registry seems (more often than not) to lose explicit paths to various objects. To verify the correctness of my theory, visit the MS-SQL job(s) in question and then edit them, specifying the precise path to the MSDORA stuff. I could be wrong, and since I don't have Oracle installed on my development box, I cannot readily check this thesis out, but I have seen this kind of problem before and I think that it concerns MS-SQL's patch updates. It could also be the case that the same problem applies to Oracle updates, but I can't readily verify this. Anyway, try visiting your job and editing it to point precisely to the ORA instance. Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Lavsa, Rich Sent: October 3, 2005 11:17 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] unable to begin a distributed transaction Hello all, We have had oracle 8i, (8.1.7.4) for 3 years or so. I was in the process of building a small application that would periodically pull data from our ERP system (oracle database) into SQL server 2K. I set up a Linked server, created a procedure, and (not to revisit all the little issues..) I was done. Now we've upgraded Oracle to 9i. We were using Net8 as the oracle client up until last week, of which all machines and servers were upgraded to Net9. Now I get the 'MSDAORA' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391), any time I try to change a view that is using OPENQUERY to return data from Oracle. I had a scheduled procedure running without any incident until we updated the Oracle 9 client. I tested the linked server, it does show all the tables and views and If I create a new view or write an OPENQUERY sql statement it all works however it will not allow me to save the view nor will the procedure work any longer. I did some research and found a few articles about "Oracle Manager for MTS". Even with some research that I did, I am not quite sure what this is and if I even need it. Anyone out there have any idea as to what my problem might be. Thanks in Advance, Rich _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com