Julia P. from the Learning Tree Transact-SQL Programming course writes “I’ve tried to use the OPENROWSET function to query Excel files, but I keep getting the same error. No matter what I try, SQL Server responds with “OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Could not find installable ISAM.”.”
Hi, Julia. Unfortunately,lots of things that aren’t quite right will result in this same error message, so let’s break this question into two parts. First, we’ll look at the many options for system configuration. Then, we’ll use that knowledge to get OPENROWSET to work.
Unfortunately, there are many possible permutations and combinations for a working system. The important points are:
Are you using 32- or 64-bit SQL Server?
Do you have Microsoft Excel installed on the same machine as SQL Server?
If you don’t have Excel installed, have you installed the Access database engine?
Which Bitness are You?
Are you running a 32-bit version of Windows Server?
If the answer is yes, then everything is easy to figure out. If you have Excel installed on the server, it must be the 32-bit version. You need install nothing else. If you do not have Excel installed, you must download and install the 32-bit version of the Access database engine. At the time of this writing (February 2015) the Access 2010 database engine can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=13255. This version of the engine will work for Excel 2013 files as well.
If you are uncertain whether the Access engine is already installed, the easiest way to check is to look in the Control Panel | Programs and Features window.
If you are working in the 32-bit world, you will only be able to link Excel files that have been saved in “Compatibility Mode”, that is, “xls” files. The more modern xlsx files will not work.
Now it becomes important whether you are running a 32- or 64-bit version of SQL Server. If you are using a 32-bit version of SQL Server you will be roughly in the same situation just described; you will only be able to use the 32-bit library to connect to Excel files and you will only be able to read xls files, not xlsx files. This is true even if you have 64-bit Excel installed on the server. If you are running a 64-bit SQL Server, you must download the 64-bit Access engine, due to threading limitations when running 32-bit code.
The only software that is actually required to query from Excel files is the Access Database Engine. As we have seen, you should be able to determine whether the engine is installed by looking in the Control Panel | Program Features applet. If you see the engine listed, look in Program Files and Program Files(x86) to see where the Microsoft Office folder lives. That will tell you whether you have the 32- or 64-bit edition. Unfortunately, the version number visible in the Programs And Features applet will not tell you the bitness of your engine. The easiest way to check your bitness is simply to see where the “Microsoft Office” folder is installed. As with applications, the 32-bit installation will create a folder in \Program Files(x86) and the 64-bit installation will create a folder in \Program Files.
If you do not see the engine, you will have to download and install it; fortunately that is easy and fast.
Clearly, if you are running a 32-bit version of Windows, there is no decision to be made. You must download the 32-bit installer. If you are running 64-bit Windows your choice will be determined by your SQL Server. A 32-bit SQL Server requires the traditional “Jet” engine, and a 64-bit SQL Server requires the “ACE” library.
Once you have installed the correct Access engine to work with your SQL Server, you are ready to query linked worksheets.
Stay tuned for part 2 where we will look at how to permanently define a linked server in SQL Server.