Exception request when called from C # code
I have the following request 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 works fine when it starts up its management studio but throws the following error when called from C # code.
System.Data.SqlClient.SqlException (0x80131904): The requested operation could not be performed because the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the requested transaction interface. Microsoft.ACE.OLEDB.12.0
I have set the following config 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 everyone seems to have discussed running it in SQL.
Any help would be much appreciated
source to share
I found a 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 "
The linked server does not support the required transaction interface, I forgot to mention that in a stored procedure I have this query in a transaction and it needed to specify the transaction isolation level, so I added "READ COMMITTED" to my query, no matter what was by default, it didn't work with it and it solved my problem.
Thanks everyone. Your time has been greatly appreciated.
source to share