Query throwing exception when called from C# Code

Question

I have following query in my stored procedure

SELECT * INTO my_table FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',''Data Source=C:\TEMP_EXCEL\sheet.xls;Extended Properties=Excel 12.0'')...Sheet1$]

It runs fine when run it management studio but throws following error when called from C# code.

System.Data.SqlClient.SqlException (0x80131904): The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface. Microsoft.ACE.OLEDB.12.0

I have set following configuration values

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

And also this

sp_configure 'show advanced options', 1;**
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;**
GO
RECONFIGURE;
GO

I have googled for this solution but all seem to have been discussing about making it run in SQL.

Any help will be much appreciated

Answer 1

I found solution to my problem. As mentioned in the error:

"Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface. Microsoft.ACE.OLEDB.12.0"

Linked server doesn't support the required transaction interface, I forgot to mention that in stored procedure I have this query in transaction and it needed to have transaction isolation level mentioned with it so I added "READ COMMITTED" to my query, whatever was default, it wasn't working with it and it solved my problem.

Thanks all. Your time was much appreciated.