Excel into SQL Server with Microsoft.ACE.OLEDB.12.0

I’m getting the following error when trying to open an Excel file in SQL Server 2008 r2 64-bit:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" 
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider 
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I’m using the following query:

  • sql server equivalent of Oracle High Water Mark
  • CONCAT'ing NULL fields
  • Create view across multiple databases
  • Will TSQL return faster results than stored procedure in SQL Server
  • Finding sql date with account year and Quarter
  • Combining Multiple results without duplication
  • SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; 
    HDR=NO; IMEX=1; Database=\\filepath\filename.xlsx', 'SELECT * FROM [Sheet1$]')
    

    The funny thing is that the DBA can run it without issue. I’ve gone through and ran the following queries:

    sp_configure 'Show Advanced Options', 1;
    RECONFIGURE;
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO
    
    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
    

    The account that runs it looks like it has sa access. What could be causing this issue?

  • Better way to write a script
  • SQL show all rows even if no matching criteria
  • Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
  • Count by unique ID, group by another
  • Change nullability add primary key in one statement
  • SQL ID from table posts same ID to all other coldfusion entries per transaction
  • 3 Solutions collect form web for “Excel into SQL Server with Microsoft.ACE.OLEDB.12.0”

    have you tried (as a test) copying the Excel file onto the SQL Server C:\ drive and executing the query against that path?

    what happens when you go onto the server and open this path in Explorer/run dialog: \filepath\filename.xlsx?

    Are you able to execute this query: exec master..xp_cmdshell ‘dir ‘\filepath\filename.xlsx’?

    This will help you determine if it’s a network rights issue, or whether the account has the permissions to use distributed queries.

    My hunch is that it’s definitely a rights/permission issue, as the DBA can run it.

    As Philip has said…first check the execution of xp_cmdshell. If it is not running due to permission issue then first reconfigure this option by running

    SP_CONFIGURE 'XP_CMDSHELL',1
    GO             
    RECONFIGURE
    

    after this run following command to enable linked server permissions for InProcess capabilities for ACE driver :

    USE [master]
    GO
    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
    

    Now run this series of commands :

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

    if error encountered then run each command separately. And finally run import all your excel data to SQL server by running the below mentioned command :

    SELECT * INTO TargetTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                             'Excel 12.0;Database=FilePath\fileName.xlsx;HDR=YES',
                             'SELECT * FROM [sheetName$]')
    

    Remember that in case of xls you have to use Jet Driver instead of ACE. And also the TargetTableName must not be existing prior to running this query.
    Happy coding 🙂

    SQL Server Management Studio. Type Services.msc in the run command to open the services window.

    Search for SQL Server Service and right click it and select properties.

    In the Log On Tab, select system account/or select your domain ID and Account and password.

    Once it finds your login name press OK.

    Now type your login’s passwords in both the fields.

    Restart the services so that the new changes are applied as shown in figure below.

    Now start SQL Server Management Studio and try to run the query if still not working try a system restart.

    or execute the query.

    USE [master] GO 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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.