Query throwing exception when called from C# Code

▲
3
▼

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

c#
sql
sql-server
asked on Stack Overflow Aug 21, 2014 by Imran • edited Aug 21, 2014 by Soner Gönül

2 Answers

▲
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.

answered on Stack Overflow Aug 22, 2014 by Imran
▲
0
▼

This is an old issue, but we had this error after we accidentally upgraded an existing code to use EF 6.1.2 Workaround for us was setting EnsureTransactionsForFunctionsAndCommands to false just before calling any Stored Procedure:

 using (Ef6Context db = NewContext())
 {
     db.Configuration.EnsureTransactionsForFunctionsAndCommands = false;
     // Call an imported SP
 }
answered on Stack Overflow Aug 20, 2018 by Babak Farahani

User contributions licensed under CC BY-SA 3.0