February 7, 2009 at 9:14 pm
I'm trying to create a query that can read data from an EXCEL 2007 worksheet. I've installed the appropriate drivers downloaded from
but I can't get it to work. The following query timesout
Select *
from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=d:\dasdgigs.xls;', 'SELECT * FROM [dasdgigs$]')
Using the old 2003 driver works fine using the following query I can successfully reads the data in the dasdgigs.xls sheet.
Select *
from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)}; DBQ=d:\dasdgigs.xls;', 'SELECT * FROM [dasdgigs$]')
I've tried the obvious things, like uninstalling and reinstalling the drivers, rebooting etc. but I just can't make any progress.
Using SQL 2005 Developers Edition.
Does anyone know what I may be doing wrong?
February 8, 2009 at 5:11 am
Try the following snippet....
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=d:\dasdgigs.xls', 'SELECT * FROM [dasdgigs$]')
--Ramesh
February 8, 2009 at 1:38 pm
Thanks for that.... it works like a charm. 🙂
February 9, 2009 at 8:33 pm
Well it worked like a charm on my development machine Windows XP, latest SP etc. I'm now trying to get it to work on windows VISTA machine latest SP etc.. I've installed the AccessDatabaseEngine but when trying the following query in SSMS.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML;Database=c:\rmfintrv.xlsx;IMEX=1;HDR=Yes;', 'SELECT * FROM [rmfintrv$]')
I get the following error
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.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Interestingly it does manage to determine the columns, if I look in the results tab I can see the column names, which proves that it can read the file.
Has anyone had any simmilar issues?
February 10, 2009 at 7:06 am
I guess it has to do with some permissions on file, though you said it can access the schema.
Try granting the read/write access to the folder to the SQL service account..
--Ramesh
February 10, 2009 at 4:11 pm
Now I'm just getting confused.
First of all I checked the authority on the directory containing the file and Everyone has Full Control so it can't be that.
I'm testing this on three machines one running VISTA native, another running VISTA under VMWARE and a SERVER 2003 machine (WHich has always worked). This morning the machine running VISTA native works fine. The VMWARE machine is still having the same issue.
I've run Process Monitor from sysinternals and have noticed that the following (quite possibly significant) registry key is missing from the failing machine but is present on the working one.
9RegOpenKeyHKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0NAME NOT FOUNDDesired Access: Read
I exported it from the working machine added it to the failing machine and tried again after bouncing the SQL instance. Now I get
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParametersNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NestedQueriesNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\AllowInProcessNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NonTransactedUpdatesNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\LevelZeroOnlyNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\IndexAsAccessPathNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DisallowAdhocAccessNAME NOT FOUNDLength: 144
RegQueryValueHKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\SQLServerLIKENAME NOT FOUNDLength: 144
These keys are not present on the working machine. If I remove the HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0 keys from the working machine it fails. The working machine does have a bunch of other stuff that could be impacting these comparisons, like SQL2008 and SSIS where as the failing VMWARE based machine only have VISTA and SQL Server 2005. I've searched the working machine registry for "DynamicParameters" (the first of the missing values) but it's not there. I also (as previously stated) have XP and Server 2003 machines where this all works properly, I've searched these registries for "DynamicParameters", also not found.
So I added HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess to the registry as a DWORDs and set their values to 1. IT NOW WORKS.......Still getting NAME NOT FOUND on the other values but it does read the spreadsheet.
To summarise, using Microsoft.ACE.OLEDB.12.0 and openrowset on XP or server 2003 machines seems to work fine on Vista machines however it seems to be missing several registry keys (Which don't appear to be present on the working machines), if the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters and AllowInProcess keys are added it works.
While I'm rather pleased that I managed to get this mess working I'm concerned about releasing my applicaiton into production when I don't really understand what caused the problem.
Any suggestions anyone on why this might be the case?
I'm a local and domain administrator on all machines and in all cases SQL Server is running under the local system account.
February 11, 2009 at 7:09 am
...Hmmm, looks like these registry keys are created to restrict the ACCESS to certain PROVIDERS within SQL Server.
--Ramesh
November 6, 2009 at 9:22 am
Hi
I have been having the same issue and found a solution for reading Data in from Excel Files.
To achive this,
The Sheet name in Excell must not have spaces,
The Directory the file is in must be accessible to the SQL Server Service user Account
The 'Ad Hoc Distributed Queries' Advanced SQL Config option must be enables
USE: SP_CONFIGURE 'show advanced options',1
RECONFIGURE WITH OVERRIDE
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
Then Create the following Stored Procedure in your DB:
CREATE PROC stp_ReadXLS@file varchar(1000)
,@template varchar(300)
,@hashtable varchar(300) = 'loader_table'
,@excell_version varchar(2) = '8'
AS
DECLARE @SQL_T varchar(4000)
EXECUTE AS login ='Sup_sp_exec'
Begin Try
SET @SQL_T = 'SELECT * INTO ##'+@hashtable+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '
EXEC (@SQL_T)
END TRY
BEGIN Catch
SET @SQL_T = 'INSERT INTO ##'+@hashtable+' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) '
EXEC (@SQL_T)
END Catch
PRINT ('Populated ##'+@hashtable+' table.....')
The Stored Proc Will read in the file and create a ## TABLE with the Contents. IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one.
Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number.
DON'T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED.
Hope this helps.
May 12, 2010 at 8:29 am
I had similar issues on Windows 7 64bit OS; Lot of these issues will disappear if you are on 32 bit OS.
Take a look at this thread:
Basic things:
(1) The Microsoft.ACE.OLEDB.12.0 provider from "2007 Office System Driver: Data Connectivity Components is 32 bit". It only works on 32 bit SSMS. So I had to install 32 bit server instance and 32 bit tools to even try this out. It didn't work on the 64 bit version.
2. You will need to configure to allow "Ad Hoc Distributed Queries" for the sql server instance.
3. As mentioned in the above link, run these two t-sql stmts to create necessary registry entries( copied from above link). Without these you get the error could not fetch a row ...But surprisingly, the MSDASQL provider worked without running the following.
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
Hope this helps.
May 12, 2010 at 2:35 pm
Hi all,
Firstly, There are now 64 bit OLEDB drivers available, these were released with the release 64 bit Microsoft Office earlier this month and have been around in beta form for several months.
Secondly a warning. My original post in this topic was regarding getting the ODBC drivers to work correctly, the initial response was to use the OLEDB drivers shiped in the ACE package, this seemed to resolve my problems once I'd figured out the stuff about registry keys. However the OLEDB drivers appear to have a bug in which the don't correctly read all csv or Excel datetime values. See the following steps to reproduce ths issue.
Creating a text file called “test.csv” in your c:\temp folder with the following contents
DATETIME
2010-05-04 07:00
2010-05-04 07:00:00
2010-05-04 08:00:00
2010-05-04 07:10:10
Then using the following SQL Server statement in management studio to read the file
select *
FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=c:\temp;HDR=Yes', 'SELECT * FROM [test.CSV]')
Expected Results
2010-05-04 07:00:00.000
2010-05-04 07:00:00.000
2010-05-04 08:00:00.000
2010-05-04 07:10:10.000
Actual Results
2010-05-04 06:59:59.997 <<<error
2010-05-04 06:59:59.997 <<<error
2010-05-04 08:00:00.000 <<<ok
2010-05-04 07:10:10.000 <<<ok
This has been reported to Microsoft and they are actively working on the problem. I've tested this on 32 and 64 bit MICROSOFT.ACE.OLEDB.12.0 and on 'MICROSOFT.JET.OLEDB.4.0 all of which exhibit the problem.
I have also had an issue with the new 64 bit drivers when running multiple OPENROWSET statements simultaneously on different threads in the same process. Eventually SQL Server hangs and the only way out is to restart the SQl Server service.
October 10, 2011 at 8:46 pm
Hi,
I was able to set this up successfully on my local and on production, as long as the user credentials used on running the script is a sysad. This url ( http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm ) By Sergey Vaselenko was very helpful
It's been up and running for months already. And just all of a sudden, sometime around last week, I received a complaint from one of our users who utilizes the uploader that they can no longer upload any excel files.
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
No one is altering the script.
No one has updated the server nor my local.
All of the servers for different apps that uses the script encoutered the error. I can't even run it on my own local pc.(When I use JET as a driver on my local it still works, but can't run it using ACE driver)
I'm really confused already on what happened on the ace driver?
I'm starting to wonder if there have been any update backend from microsoft or some kind of a bug on the ace driver??
Hope someone could help me if you have similar issue.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply