Data Migration through SQL


I am trying to migrate data using SQL stored procedure and I am having success. But some stored procedures have excel import code.

select *

into #VendFirmOrders

FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,'Excel 12.0;HDR = YES; IMEX = 1;

Database=\AX Developer Documentation\Data Migration\Vendors\VEN - Email_Firm_Order & Form_Fn_Name Email_Address list - 20160310.xlsx’,

‘select * from [Email_Firm_Order$]’)

The data provider related to ‘Microsoft.ACE.OLEDB.12.0’ is configured to the linked server in SQL.

H:\ACE 12.0 Validation error.png

Error Msg:

Query validation failed-ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied. You must access this provider through a linked server.

Even then when the system is running through this code, the system is throwing the below error.

I am guessing that since we are running this code from AX side, do we have to provide this setup or configuration in any AX module ?

Or is there any other solution which you experts can provide me?

For reference, this question is already being discussed an another forum.