This post was originally published here

Another day, another error to report – still have plenty of them in my backlog to be published, they are an “easy and quick way” to publish something in my blog when I really don’t have much free time to write something different. Today is about “This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters” error message that I got when I was initially trying to connect for the first time to ORACLE database to insert some data:

Microsoft.ServiceModel.Channels.Common.MetadataException: Metadata resolution failed for OperationId: “http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/TRANSACTIONS/Insert”. —> Microsoft.ServiceModel.Channels.Common.ConnectionException: Due to an Oracle Client limitation, the adapter failed to open a connection. This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters, or (b) ambient transaction is present and a non-TNS based URI was used. To resolve this, use a TNS alias to connect to Oracle and make sure it is not more than 39 characters.

at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceCh.

This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

Followed by other similar warning messages:

A message sent to adapter “WCF-Custom” on send port “INSERT_PAYMENTS_WCFORACLE” with URI “oracledb://IP-ADDRESS:PORT-NUMBER/PATH” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.MetadataException: Metadata resolution failed for OperationId: “http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/PAYMENTS/Insert”. —> Microsoft.ServiceModel.Channels.Common.ConnectionException: Due to an Oracle Client limitation, the adapter failed to open a connection. This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters, or (b) ambient transaction is present and a non-TNS based URI was used. To resolve this, use a TNS alias to connect to Oracle and make sure it is not more than 39 characters.

at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

MessageId: {DE7ABF70-D6B7-4FC8-A570-5AAE4FFACBB9}

InstanceID: {5842C3C7-6746-4A56-8707-FF53123A4101}

Cause

Non-TNS based URI is not supported under an ambient transaction. If you have to use transactions, you should use the TNS alias.

Note: TNS Alias needs to be less than 39 characters.

Solution

To solve this problem, you need to:

  • Open the send port properties, by double-clicking on the port;
  • Click on “Configure…” button under Transport
  • On the Transport Properties window, select the “Message” tab and then uncheck the “Use Transaction” box under Transactions

This is because either (a) ambient transaction is present and the TNS alias is longer than 39 characters

If you try to resend the message this problem should be solved. Additionally, if this error still persists, you should change the following properties in the oracleDBbinding:

  • “enableBizTalkCompatibilityMode” = True (this is set to false per default)
  • And/or “useAmbientTransaction” = False
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira