How do I keep an OleDbConnection from trying to enlist in a distributed transaction?

Question

I am using OleDB to connect to an excel file using this connection string

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"""

But when I do this (which is inside a TransactionScope())

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    ...
}

I get the following error

The ITransactionLocal interface is not supported by the 'Microsoft.ACE.OLEDB.12.0' provider. Local transactions are unavailable with the current provider.

How do I make the OleDbConnection not try to enlist in the distributed transaction? The SqlConnection class has a ConnectionString property called 'Enlist', but I can't find an equivalent configuration or method for OleDB.

Overzealous

But this is a local transaction right? The error is talking about local one only and not distributed one. I am not sure about your case but may be my post here [faiz.kera.la/2009/08/26/… may help you. – Smattering

yes, the error says local transaction, but there is not an explicit local transaction, just the TransactionScope – Overzealous

Answer 1

In your connection string add the following code : ";OLE DB Services=-4;"

Pantalets

This is a fantastic solution that saved my bacon too! I hate how this stuff is so undocumented ... I'd like to know how you know it Reza? – Quinnquinol

I know this is 3 years old, but I stumbled upon this solution and wondered what's behind it. It disables ole db pooling and auto-enlistment. More information here, in Table 4: msdn.microsoft.com/en-us/library/ms810829.aspx – Nomothetic