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.