There are some challenges that you might experience reading excel files running SQL server on x64 machines. Usually, everything works fine on 32-bit systems, but apparently there are some issues on 64-bit system though.
Here is the most often error appearing and definitely it doesn't tell us to much about the real cause of the problem:
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.
SYSTEM REQUIREMENTS:
The following solutions could be helpful in the most cases, so follow all of these steps and requirements as well.
Requirements for 64-bit system are:
- Remove all x32 Office and Microsoft Access Database Engine x32 from your machine (sometimes both of these should be uninstalled to get )
- Install Microsoft access database engine 2010 x64: http://www.microsoft.com/en-ca/download/details.aspx?id=13255
- Optionally, install x64 Office if you previously uninstalled it in step 1
ENABLE AD HOC QUERIES AND OLEDB PROPERTIES
/*AD HOC QUERIES*/
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Office 2010:
/*OLEDB PROPERTIES*/
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
Office 2013:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1
OPENROWSET QUERIES
Office 2010:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\temp\test.xlsx;HDR=NO;IMEX=1', 'SELECT * FROM [Sheet1$]')
Office 2013:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 'Excel 12.0;Database=c:\temp\test.xlsx;;HDR=NO;IMEX=1', [Sheet1$])
ADITIONAL TROUBLESHOOTING
It's not recommending, try running SQL Server Management Studio as Administrator (Right Click and Run As Administrator)
Mike C.