Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.

Diana Mae Benitez

Question

Hi,

I'm trying to upload an Excel file through outsystems, and the file will be stored in a certain folder that will be access by the stored procedure being executed in Outsystems. I already did some configurations in database in terms of granting OSRUNTIME permisssion to execute, still got the same error, the Microsoft.ACE.OLEDB.12.0 already exist as well on Server Objects>Lonked Providers>Providers in Database. I also tried to run the 'select * from openrowset..' and it was able to show data from excel file. I don't know if it's the database has something to do with this or the Outsystems. I already tried to do the ff: suggested from different sites i researched, yet got the same error.

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

	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

	    , N'AllowInProcess', 1

GO

	EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

	    , N'DynamicParameters', 1

	GO

	Thanks,

09 Feb 2015

João Rosado

Staff

Hi Diana,

From googling for the error that is also a change needed in the windows registry that may be required: https://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled

But, why do you need to do it at the database level? Doesn't the built-in ExcelToRecordlist do what you need?

Regards,
João Rosado

09 Feb 2015

Diana Mae Benitez

Hi Joao,

Thanks for your response. It works out fine, I just added the "DisallowAdhocAccess" in registry key for Microsoft.ACE.OLEDB.12.0. We used the ExcelToRecordlist before, but the excel file mostly contains 100k rows that needs to undergo certain process and took a lot of time, which actually returned a 'Request Time-Out', what we did was transforming the logic to a stored proc, which basically do the process triggered in Outsystems, that only took 5-10 secs to process.

Thanks,
Diana

10 Feb 2015

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines