Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Openrowset access to Excel 2007 Expand / Collapse
Author
Message
Posted Saturday, February 7, 2009 9:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:41 PM
Points: 40, Visits: 2,470
I'm trying to create a query that can read data from an EXCEL 2007 worksheet. I've installed the appropriate drivers downloaded from

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

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?
Post #652343
Posted Sunday, February 8, 2009 5:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
Try the following snippet....
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=d:\dasdgigs.xls', 'SELECT * FROM [dasdgigs$]')



--Ramesh

Post #652387
Posted Sunday, February 8, 2009 1:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:41 PM
Points: 40, Visits: 2,470
Thanks for that.... it works like a charm. :)
Post #652452
Posted Monday, February 9, 2009 8:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:41 PM
Points: 40, Visits: 2,470
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?

Post #653375
Posted Tuesday, February 10, 2009 7:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
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

Post #653670
Posted Tuesday, February 10, 2009 4:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:41 PM
Points: 40, Visits: 2,470
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.
9RegOpenKey HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0 NAME NOT FOUND Desired 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


RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DynamicParameters NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NestedQueries NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\AllowInProcess NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\NonTransactedUpdates NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\LevelZeroOnly NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\IndexAsAccessPath NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\DisallowAdhocAccess NAME NOT FOUND Length: 144
RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.ACE.OLEDB.12.0\SQLServerLIKE NAME NOT FOUND Length: 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.
Post #654326
Posted Wednesday, February 11, 2009 7:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
...Hmmm, looks like these registry keys are created to restrict the ACCESS to certain PROVIDERS within SQL Server.

--Ramesh

Post #654784
Posted Friday, November 6, 2009 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:10 AM
Points: 7, Visits: 188
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.
Post #815001
Posted Wednesday, May 12, 2010 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 8:14 AM
Points: 6, Visits: 41
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:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

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.
Post #920532
Posted Wednesday, May 12, 2010 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:41 PM
Points: 40, Visits: 2,470
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.
Post #920864
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse