Hi All,
I was referred to this forum from a post in the general forum, apologies for the duplication. (
9652156
I have been struggling now for a couple of days with an issue and I am at the end of my tether, can anyone help me with the following issue:
Environment:
This is all being run locally on my laptop with Windows 7 Professional SP1
Oracle 11g (11.1.0.6.0) Enterprise Edition
SQL Server 2008
Problem:
I am trying to transfer data to my SQL database from an Oracle database using a linked server (OraOLEDB.Oracle Provider) using a stored procedure within my application and I get the message:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "SERVERNAME" was unable to begin a distributed transaction.
Resolutions tried so far:
-I am able to connect to the Oracle database using SQLPLUS and by testing the Linked Server connection
-I can query the Oracle database using a select statement from SQL by referencing the Linked Server
-The SQL Query that is running from my application runs fine if I run it directly from a query within SQL
-MSDTC Service is running (as are my Oracle services)
-Windows Firewall is disabled and I have allowed MSDTC access through it anyway
-I have enabled all the checkboxes under Security in the MSDTC component services to Enable DTC Access, Allow Remote Clients, Allow Remote Admin, Allow Inbound, Allow Outbound, No Authentication, Enable XA Transactions, Enable SNA LU 6.2 Transactions and the DTC Logon account is the NT AUTHORITY\NETWORK SERVICE
-I have checked the registry to ensure the HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC\MTxOCI lib dll values are for Oracle 11 (oci.dll, orasql11.dll & oraclient11.dll)
-I have enabled the 'Allow InProcess' under the OraOLEDB.Oracle Provider in SQL Server Objects
-I have tried using the Microsoft Oracle driver just in case but this gives a similar message
-The SQL Query runs an insert into the SQL database by selecting data from an OPENQUERY transaction data set, as previously stated, if I run this statement outside of the distributed transaction on its own, it works. This query also works in another environment so im positive my issue is environment related and not query related.
-I have installed the Oracle Windows Interfaces component of the Oracle 11g application
The sad part of this whole situation is that I previously had this running fine on the same machine but I formatted and reinstalled the OS so this is now occurring in a clean install.
I cannot think of anything else to add to all of that, so any assistance would be greatly appreciated.
Regards
Chris