Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

Using TransactionScope with ODP.NET

If you are using Oracle Database Provider for .NET (ODP.NET) and you want to use TransactionScope'd transactions, you may have gotten the dreaded "Data provider internal error(-3000) [System.String]" exception. I'll explain how I fixed it.

First, if you want to support distributed transactions, make sure you have the OracleMTSRecoveryService service installed and running on your machine (it comes with the Oracle Data Access Components installation), or any other machine on your network, as long as properly configured on the MSDTC tab on the Component Services applet.

If you don't need distributed transactions, you can place the connection string setting PROMOTABLE TRANSACTION=LOCAL on your connection string, for example:

DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.Some.Community)(PROTOCOL=TCP)(HOST=Some.Host)(PORT=1522))(CONNECT_DATA=(SID=SomeSid)));USER ID=SomeUsername;PASSWORD=SomePassword;PROMOTABLE TRANSACTION=LOCAL

Now you can have:

using (TransactionScope tx = new TransactionScope())

{

    using (OracleConnection con = new OracleConnection(@"DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=TCP.Some.Community)(PROTOCOL=TCP)(HOST=Some.Host)(PORT=1522))(CONNECT_DATA=(SID=SomeSid)));USER ID=SomeUsername;PASSWORD=SomePassword;PROMOTABLE TRANSACTION=LOCAL"))

    using (OracleCommand cmd = con.CreateCommand())

    {

        con.Open();  //no longer crashes here

        cmd.CommandText =
"SELECT sysdate FROM dual";        DateTime date = (DateTime)cmd.ExecuteScalar();

    }

}

If you need more information, check the Oracle Data Provider for .NET Developer's Guide, it's the e10927.pdf file on the B28359_01\win.111 folder of the Oracle documentation (version 11g).

Comments

Robert Durgin said:

Thanks, this helped! I've been trying to resolve the same issue for the past week!

# April 27, 2009 8:12 AM

Ankit Dass said:

When i use PROMOTABLE TRANSACTION=LOCAL in my connection string i get an exception saying, PROMOTABLE TRANSACTION is nto a valid connection string identifier. I ma using oracle.DataAccess 9.2 dll

Am i missing something?

# September 16, 2009 6:56 AM

Ricardo Peres said:

Ankit,

It's probably not supported, I am using 11g... please confirm on the Oracle documentation, or upgrade to a newer version.

# September 16, 2009 3:56 PM

marcal said:

I have used 'Enlist' parameter in ConnectionString.

In my Vista it works.

Follow example:

"Password=<PSSWRD>;User ID=<USER>;Data Source=<DSN>;Persist Security Info=True;Enlist=false"

# January 6, 2010 7:15 AM

Adam said:

@A2

Using Enlist=false removes the option of having distributed transactions

# March 2, 2011 10:05 AM