Arthur Fuller
artful at rogers.com
Mon Oct 3 17:33:09 CDT 2005
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