Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Unable to begin a distributed transaction

user3514542Jun 9 2011 — edited Jun 9 2011
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

Comments

gdarling - oracle
Is this intermittent or consistently reproducible?

When you say "MSDTC Service is running (as are my Oracle services)", is OracleMTSRecoveryService one of them? My WAG is that you don't have the Oracle Service for Microsoft Transaction Server installed on your cleint. If you check Services, do you see "OracleMTSRecoveryService"? If not, rerun the client install, choose Custom, make sure to select your existing home, and add it.

Note that the recovery service isnt needed to actually participate in distrib transactions (it's only used for recovery) , but it gets installed at the same time as the other pieces, so if you're missing the recovery service, you're also missing the other pieces.

Hope it helps,
Greg
user3514542
Hi Greg,

Thanks for the response.

The problem is reproduce able every time.

That service is there and I did install the Oracle Service for Microsoft Transaction Server specifically from the custom components (although I was not aware it creates that service, so I have learnt something new)
Klibby-Oracle
What else is going on within your stored procedure? Most of the time people do not write a stored procedure when a query is sufficient.
gdarling - oracle
Ok, looking a little closer at your previous data points:

1) Using Microsoft's OLEDB also fails, and Microsoft's and Oracle's OLEDB providers use very different mechanisms when it comes to distributed transactions. That doesnt solve the problem for us, but is interesting.

2) 11.1.0.6 is not supported on Win7. 11.1.anthing is not, for that matter. 10205, 11201, and 11202 are the supported versions.
That being said though, you might want to try patching up to 11107 client. you might be runing into this bug, fixed in 11107
Using ORAMTS on Microsoft Vista Results in The Error ORA-161 (Doc ID 467181.1)
The better solution though is to use a supported config, and you'll already have the fix.

3) The next step is probably to get sqlnet traces and see if there's a better error message.


SQLNET Tracing:
=============
If you are unsure how to obtain a trace, place the following entries in the client Sqlnet.ORA file (should be in %Oracle_home%\network\admin). Make sure before you start your application that the C:\Trace directory exists.

TRACE_FILE_CLIENT = nettrace
TRACE_DIRECTORY_CLIENT = C:\trace
TRACE_UNIQUE_CLIENT = OFF
TRACE_LEVEL_CLIENT = 16
TRACE_TIMESTAMP_CLIENT = ON
DIAG_ADR_ENABLED=OFF

Save Sqlnet.ora, and restart the application if needed as the tracing parameters only get picked up when the connection is first established. Reproduce the problem behavior. Back in Sqlnet.ora, set TRACE_LEVEL_CLIENT = OFF to turn off tracing, and save Sqlnet.ora. Stop or restart the app again so tracing does not continue.

4) If you have a support contract, open a SR. However, you'll probably want to get into a supported configuration before doing that.

Greg
user3514542
One thing I forgot is the stored procedure calls a sql query which extracts the data from Oracle to transfer to SQL but at the end it sends an update back to Oracle so perhaps that is where the issue comes from.

Im still convinced this is an environment issue as I had this exact same system working prior to my reinstall and the queries/stored procedure have not changed.

I am going to be out of the office for a couple of days but I will try the software updates and see if that helps when I get back, I may have done this previously and just forgotten that I did (anything is possible!)

Thanks for the assistance, I will post an update after I have tried it to see if it worked or not.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 7 2011
Added on Jun 9 2011
#oracle-provider-for-ole-db, #windows-and-net
5 comments
3,251 views