OPENROWSET and "Could not find installable ISAM"

Question

OPENROWSET and "Could not find installable ISAM"

kevaburg

SSCoach

Points: 18131
More actions
March 16, 2018 at 1:02 am

#403182

Hi Folks,
I have a problem with regards to querying an Excel Spreadsheet using not only OPENROWSET but also OPENDATASOURCE.
I am using SQL Server 2016, the spreadsheet is Excel 2010.
This is the OPENROWSET statement:
select * from openrowset( 'Microsoft.ACE.OLEDB.16.0', 'Excel 15.0;Database=C:\test.xlsx;HDR=YES;IMEX=YES', 'select * from [Sheet1$]'
On the machine I was originally developing the query this worked fine with no issues at all. On the machine it is supposed to work on I get a "Could not find installable ISAM." error. This is followed by a Msg 7303, could not initialize datasource.....
What I have done to date:
1. Deinstalled and reinstalled the ACE providers, version 12.0 and 16.0 and removed all appropriate registry entries after the deinstall
2. Altered the Excel parameter incrementally from 12 up to 16
3. Checked the registry values at
HKLM\SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQL14.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.16.0
for both providers to ensure they are there
4. Enabled 'Allow InProcess' and 'Dynamic Parameter'
5. Tried using both the ACE12 and ACE16 providers
None of this has worked and my relentless searching through the Internet yielded nothing other than what I have listed here.
This seems to be a relatively common issue but I can find nothing that can help me resolve my problem.....
Does anyone have any other ideas?
Regards,
Kev

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110639 More actions · Answer 1

It's that kind of thing that usually forces me into SSIS. And at times, when even SSIS balks, back to VBScript. Push comes to shove, an install of Excel on the target machine, if not already there, may have an impact.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

One thing I've read about the error before is that the IMEX settings can cause different errors. I've never used YES - the values I've seen are 0 - 3. Does anything change if you set that to 1?

Sue

Joe Torre SSChampion Points: 10248 More actions · Answer 3

Check this out and see if it helps solve the issue.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

sgmunson - Friday, March 16, 2018 6:34 AM
It's that kind of thing that usually forces me into SSIS. And at times, when even SSIS balks, back to VBScript. Push comes to shove, an install of Excel on the target machine, if not already there, may have an impact.

I can tell you that I'll never allow Excel to be installed on a production server and I don't care if it's the CIO, CEO, or anyone else saying "Just do it". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

kevaburg - Friday, March 16, 2018 1:02 AM
Hi Folks,
I have a problem with regards to querying an Excel Spreadsheet using not only OPENROWSET but also OPENDATASOURCE.
I am using SQL Server 2016, the spreadsheet is Excel 2010.
This is the OPENROWSET statement:
select * from openrowset( 'Microsoft.ACE.OLEDB.16.0', 'Excel 15.0;Database=C:\test.xlsx;HDR=YES;IMEX=YES', 'select * from [Sheet1$]'
On the machine I was originally developing the query this worked fine with no issues at all. On the machine it is supposed to work on I get a "Could not find installable ISAM." error. This is followed by a Msg 7303, could not initialize datasource.....
What I have done to date:
1. Deinstalled and reinstalled the ACE providers, version 12.0 and 16.0 and removed all appropriate registry entries after the deinstall
2. Altered the Excel parameter incrementally from 12 up to 16
3. Checked the registry values at
HKLM\SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQL14.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.16.0
for both providers to ensure they are there
4. Enabled 'Allow InProcess' and 'Dynamic Parameter'
5. Tried using both the ACE12 and ACE16 providers
None of this has worked and my relentless searching through the Internet yielded nothing other than what I have listed here.
This seems to be a relatively common issue but I can find nothing that can help me resolve my problem.....
Does anyone have any other ideas?
Regards,
Kev

Did you check under linked servers in the explorer window of SSMS and verify that the drivers are listed under "Providers"?

Also, how are you logged into the machine that it's supposed to work on? I ask because the Kerberos double hop "problem" can sometimes get you. My recommendation would be to write the code as a proc and schedule it for a single run and see if it works that way. If it does, then you've found the "problem", which may only be a problem during development.

Also, why are you storing any of your junk on the C: drive of your server, especially in the root directory? Is that where the file actually lives on the server because your code is saying to look for it there. If the spreadsheet lives on the C: drive of some other box, then you need to setup a share and refer to it using a UNC instead of a drive specification.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323504 More actions · Answer 6

Jeff Moden - Friday, March 16, 2018 3:33 PM
sgmunson - Friday, March 16, 2018 6:34 AM
It's that kind of thing that usually forces me into SSIS. And at times, when even SSIS balks, back to VBScript. Push comes to shove, an install of Excel on the target machine, if not already there, may have an impact.
I can tell you that I'll never allow Excel to be installed on a production server and I don't care if it's the CIO, CEO, or anyone else saying "Just do it". 😉

Don't install Excel/Office, just the ACE drivers, which is just another provider.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevaburg SSCoach Points: 18131 More actions · Answer 7

Hi Folks,

thanks for your help and sorry I haven't answered sooner. I am still fighting to make this work and have decided to leave it unresolved for the time being and use SSIS as an interim solution.

@sg_munson: I can't install Excel on the server just to see if it works. I regard that as the worst sort of workaround for a problem Microsoft should have sorted by now. Microsoft documentation itself says that only the installation of the ACE provider should suffice.

@sue: Thanks for the headsup. I have have the numerical values 0-3 but it makes no difference. The problem as described still persists.

@jeff: Your comment about installing Excel is 100% right and it can't be considered a viable solution. With regards to your other comments and questions:
1. The providers are listed as I would expect them to be and they rae configured corrected as per Microsofts recommendations.
2. I have tried logging on locally and remotely but it makes no difference; the problem persists.
3. The path as I have shown it here is only for the sake of the post. The file itself is referenced, as you pointed out, by a UNC path to a shared folder.

@lowell: The ACE drivers are installed, the "Allow Dynamic" and "Allow In-process" are checked. There are no errors inthe logs relating to any problems identified by SQL Server or the operating system.

I find it remarkable that after so long on the market Microsoft stil lhas so many problems making Microsoft products work wit other Microsoft products.....

Thanks for all your help. Maybe I will find a solution....

Regards,
Kev

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

@ kevaburg ,

Thanks for the feedback. I guess the only thing left would be to ask if someone, perhaps, installed the 32 bit version on a 64 bit machine, which could cause similar problems with no resolution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323504 More actions · Answer 9

ok i was able to duplicate the error and a resolution. here's the tiny differences.
I created am XLSX document , with a default sheet name of Sheet1.
i used this command, and got the same ISAM error.
Note my file does not exist in C:\, but in a sub folder. your example implied it was in the root of C:

SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;DATABASE=c:\Data\Lowells_ListOfServers.xlsx;IMEX=1', 'Select * from [Sheet1$]')
I installed AccessDatabaseEngine so I could have the 32 bit version, and then I installed AccessDatabaseEngine_X64.exe with the /passive flag, so i can have both 32bit and 64 bit drivers
D:\SQLInstalls\AccessDatabaseEngine_X64.exe /passive

Then i reset the AllowInProcess and DynamicParamets again just in case:
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

The identical code above then worked successfully.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

Lowell - Monday, March 19, 2018 7:50 AM
ok i was able to duplicate the error and a resolution. here's the tiny differences.
I created am XLSX document , with a default sheet name of Sheet1.
i used this command, and got the same ISAM error.
Note my file does not exist in C:\, but in a sub folder. your example implied it was in the root of C:
SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;DATABASE=c:\Data\Lowells_ListOfServers.xlsx;IMEX=1', 'Select * from [Sheet1$]')
I installed AccessDatabaseEngine_X64.exe with the /passive flag, so i can have both 32bit and 64 bit drivers
D:\SQLInstalls\AccessDatabaseEngine_X64.exe /passive
Then i reset the AllowInProcess and DynamicParamets again just in case:
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
The identical code above then worked successfully.

I was under the impression that the OP had already made those settings changes. Definitely worth double-checking though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevaburg SSCoach Points: 18131 More actions · Answer 11

Jeff Moden - Monday, March 19, 2018 9:39 AM
Lowell - Monday, March 19, 2018 7:50 AM
ok i was able to duplicate the error and a resolution. here's the tiny differences.
I created am XLSX document , with a default sheet name of Sheet1.
i used this command, and got the same ISAM error.
Note my file does not exist in C:\, but in a sub folder. your example implied it was in the root of C:
SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;DATABASE=c:\Data\Lowells_ListOfServers.xlsx;IMEX=1', 'Select * from [Sheet1$]')
I installed AccessDatabaseEngine_X64.exe with the /passive flag, so i can have both 32bit and 64 bit drivers
D:\SQLInstalls\AccessDatabaseEngine_X64.exe /passive
Then i reset the AllowInProcess and DynamicParamets again just in case:
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
The identical code above then worked successfully.
I was under the impression that the OP had already made those settings changes. Definitely worth double-checking though.

Actually I haven't tried using the /passive switch. Everything else I have done. I will try the switch at teh next opportunity. Maybe it will help.

During the cleanup of the registry though I looked for entries relating to ACE.OLEDB and removed them but I am wondering whether or not I have missed something out.....