ITransactionLocal interface is not supported

Question

I have a .NET 3.5 C# library that uses OleDb to pull data from an Excel file and return it in a DataRowCollection which is used by a CLR Table-Valued Function in SQL Server 2005.

The function works fine when I use SELECT on it. But if I want to use its output as a source for an INSERT INTO a table, I get the following error:

System.InvalidOperationException: The ITransactionLocal interface is not supported by the 'Microsoft.Jet.OLEDB.4.0' provider. Local transactions are unavailable with the current provider. System.InvalidOperationException: at System.Data.OleDb.OleDbConnectionInternal.EnlistTransactionInternal(Transaction transaction, Boolean forcedAutomatic) at System.Data.OleDb.OleDbConnection.Open() at GetExcelFunction.GetFile(String strFileName) at GetExcelFunction.InitMethod(String strFileName)

So, how do I solve this? Do I stop the connection from creating the transaction? If so how? I don't see any relevant methods or properties on the OleDbConnection. Is there a parameter in the connection string?

Answer 1

add ";OLE DB Services=-4" to connection string.

Answer 2

Thanks, that fixed my error!!!