I'm getting the following error when trying to open an Excel file in SQL Server 2008 r2 64-bit:
Dec 02, 2021 In this post, I'll talk a little about the two most commonly used OLEDB drivers in SQL Server for file integrations, especially Excel, which are Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0. Once installed on the server, they allow you through the database to be able to enter, query, update, and delete Excel spreadsheet data and text. Microsoft Oledb Driver For Excel; Microsoft.ace.oledb.12.0 Driver Download 2016; The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine running windows 8.1 with sp1 x64 can someone please advise how you register it.
To this end, the driver you are missing, is actually the Microsoft Access Database Engine 2016 Redistributable. And you can download it from here. Now, when you visit the above download link and right after you click on the “Download” button, you are presented with 2 options. 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=FilePathfileName.xlsx;HDR=YES', 'SELECT. FROM sheetName$') Remember that in case of xls you have to use Jet Driver instead of ACE.
I'm using the following query:
The funny thing is that the DBA can run it without issue. I've gone through and ran the following queries:
The account that runs it looks like it has sa access. What could be causing this issue?Erran Morad
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
after this run following command to enable linked server permissions for InProcess capabilities for ACE driver :
Now run this series of commands :
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 :
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 :)
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: filepathfilename.xlsx?
Are you able to execute this query: exec master..xp_cmdshell 'dir 'filepathfilename.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.Our Man in BananasOur Man in Bananas
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
Microsoft Ace Oledb 12.0 Download Excel 20101
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008excel or ask your own question.
I investigated Microsoft.ACE.OLEDB.12.0 and have plenty of artefacts and findings. In case you haven't met this component it allows data to be read and written to Excel worksheets using SQL technology.
As far as I can see ACE stands for Access Connectivity Engine. This wikipedia article is a good web page which highlights the history of the name.
COM Registry entries
Some time back (with some help from StackOverflow) I got theATL C++ Sample OLEDB Provider compiled and working. From that experience I can tell you that every provider string is in fact a COM Prog ID. This means we call write code like this to test the installation...
Microsoft Ace Oledb 12.0 Download Excel File
It also means if we scan the registry for the Prog ID 'Microsoft.ACE.OLEDB.12.0' then we can find other details. I have placed a registry export of the COM registry entries in Appendix A.
From the details it can be seen that the ProgId is 'Microsoft.ACE.OLEDB.12.0' whilst the fuller name is 'Microsoft Office 12.0 Access Database Engine OLE DB Provider' and it is implemented in the executable ACEOLEDB.DLL. This gives us some search terms to google on.
If you need to install this then you must download the Microsoft Access Database Engine 2010 Redistributable . The accompanying explanatory text says this is not a replacement for Jet saying one should use SQL Server Express Edition but to be honest I think many of us do see Microsoft.ACE.OLEDB.12.0 as a Jet replacement.
Also on that download page there some help about how to use an Extended Property in the connection string to specify the correct file format version.
As it turns out, one can supply a wider range of values than those shown above, i.e. non-Excel file formats. Appendix D shows a screenshot of the registry which I believe shows all the valid values, they are all ISAM Formats.
Pitfall - Workbooks needs to be saved
I suspect the code in the provider is contingent on the workbook's file extension and it will complain if it has no file extension. When you create a workbook, it is just 'Book1' ; it has no file extension until it has been saved at least once. This pitfall is easily countered with a line of defensive code to inspect the workbook's file extension ...
... or ...
Connection Strings Resources
An excellent resource for how to build a connection string for any data provider is www.connectionstrings.com and on that link one can see connection strings for historic versions of Excel. Also on that page are details of extended properties.
Jet Extended Properties
I'd like to compile a list of extended properties that relate to Microsoft.ACE.OLEDB.12.0 . I suspect many of them are inherited from the Jet. So here is a list of Jet extended properties courtesy of Working with MS Excel(xls / xlsx) Using MDAC and Oledb - CodeProject, a great article that I won't try and replicate.
Looks like Extended Properties needs enclosing double quotes (in some cases at least).
- HDR - Short for Header, if YES then the top row are like column headers and interpreted as field names.
- FirstRowHasNames - different way to do same as HDR
- MaxScanRows - data types are inferred from n rows, this sets n
- IMEX - I'm guessing this is short for Import/Export and is also used in column type inference
Related to IMEX is ImportMixedTypes which I have seen in an Microsoft.ACE.OLEDB.12.0 connection string but not in a Jet connection string. For Jet and Microsoft.ACE.OLEDB.12.0 ImportMixedTypes is a registry entry but it also looks like supplying ImportMixedTypes in the Microsoft.ACE.OLEDB.12.0 connection string allows an override. For explanation of ImportMixedTypes here is another great article, this time at dailydoseofexcel.com, Daily Dose of Excel - External Data – Mixed Data Types .
Pitfall - The Problem of Type Inference
So the OLEDB Provider infers a column's data type from its contents, sampling the data. I don't much like this, I'd prefer a way to specify the data type but I have yet to find a way to do this. Perhaps it is best to ensure the data in the cells is consistent, we can lock sheets and control access to ensure a user does not corrupt the data but then that creates a need to show a separate data entry form. I will mull this. In the meantime I'd advise you are very disciplined that any data you write is type consistent for that column.
Access Connectivity Engine
So I have discovered another bunch of registry entries which I placed in Appendix B. So there is another dll at work here, ACEEXCL.DLL. I will try to investigate how ACEEXCL.DLL interacts with ACEOLEDB.DLL. UPDATE: I solved this in Appendix D!
Pitfall - Pass CursorTypeEnum.adOpenKeyset or CursorTypeEnum.adOpenStatic When Opening a Recordset
Even after correctly forming a connection strings I have still had some issues using this OLEDB provider. So in in my use case when calling the Recordset.Open method it is critical to pass the right enumeration value. CursorTypeEnum.adOpenDynamic and CursorTypeEnum.adOpenForwardOnly did not throw errors they simply returned an empty recordset! This matters because I believe one of them is the assumed default. I needed to pass either CursorTypeEnum.adOpenKeyset or CursorTypeEnum.adOpenStatic to get any rows back.
Pitfall - Better To Specify an Exact Range Than a Whole Sheet
Even after sorting a connection string and CursorTypeEnum parameter one can still get bugs. If a whole sheet is specified then it will infer data from the whole Worksheet.UsedRange. This means if you dirty your cells on the sheet (by entering anything and deleting them) then that cell and all those between it and $A$1 will be implied to belong to the table. So it is better to find the range with [A1].CurrentRegion.Address and either (1) define a name over that range and pass range name into the SQL or (2) used the explicit address of the range, e.g. $A$1:$B$3
Sample Code to Open a Recordset
So now we know where the pitfalls lie we can write some defensive sample code. This code opens a recordset and prints out its contents. PLEASE USE A FRESH NEW WORKBOOK! There is some setup code to write some data to a sheet in SetUpSomeData() so best to use a new workbook but remember to save the workbook at least once.
The code demonstrates the following points ...
- It defends against the pitfalls of unsaved workbooks;
- it supplies a working CursorTypeEnum;
- it restricts the cells to select, by two different methods (1) by name and (2) by cell address
As a bonus I have added some code in ReadExcelCatalog which demonstrates using the ADOX library to read schema information so one can tell exactly what the OLEDB provider is inferring for a column type. Enjoy!.
Appendix A - COM Registry entries
It always useful to poke around in the registry to see what makes something tick, here is a registry export of the related keys. It turns out there is a second bunch of registry keys to tune the behaviour (page down). The following set of registry keys fulfil the COM registration requirements for OLEDB providers.
Appendix B - Access Connectivity Engine Registry entries
So I have discovered another bunch of registry entries which I found after discovering this page Initializing the Microsoft Excel Driver -MSDN.
Appendix C - Access Connectivity Engine Files and Dependencies
So there is a whole bunch of files prefixed with ACE*.DLL which look related to Access Connectivity Engine, for me they are located in
which looks like some sort of virtualised file system (is that what the vfs stands for?). Anyway, here is the list
I do not know what ACEES.DLL or ACEWDAT.DLL are but all the other files we can guess at their purpose.
We know the route into the code starts with COM and ACEOLEDB.DLL (see Appendix A), looking at the entry points for ACEOLEDB.DLL we see the classic COM entry points
If we look at the entry point for ACEEXCL.DLL we see the classic COM entry points
so very much a COM DLL. I wonder what classes are created and passed out by these DLLs. OLEVIEW sheds no light on this or ACEOLEDB.DLL
Appendix D - ISAM Formats (first term of Extended Properties) Map to Engines
Below is a screenshot of the registry which I believe shows all the valid values for the first term of the Extended Properties. They are all ISAM Formats.
Looking down the list of Value Data pairs for the given key, 'Excel 12.0 Macro', we can see one entry 'Engine' with 'Excel' as the string data. The 'HTML Export' and 'HTML Import' keys also have 'Engine' Values with 'Text' as the string data. In another screenshot we can see that they must be mapping to the keys under the Engines key. I have drawn some mapping lines (sorry no arrow heads).
Let's look at what is the Excel engine key. Voila, it tells which DLL to load to handle requests for Excel in the win32 value ...ACEEXCL.DLL . The Value-Data pairs shown below in the next screenshot have already been detailed in Appendix B but it is only now that I have pieced together the logic sequence to the load the right 'engine' file.