Trying to use openrowset to import 2003 Excel file into 64 bit 2008 R2 SQL Server - by sergioza

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 581640 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 7/30/2010 8:37:20 AM
Access Restriction Public

Description

Hi,

We have a lot of functionality relying on following statement:

select * into TableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;Database=fullfilename',Sheet1$)" 

which works nicely from 32 bit SQL Server 2005 to import Excel 2003.

Now we need to do the same from 64 bit SQL2008, this driver doesn't work and new "ACE" drivers also seem to fail. Is there any driver available to do what we need.
Sign in to post a comment.
Posted by Microsoft on 11/17/2010 at 12:20 PM
Hello,

The resolution provided below regarding using the 64 bit ACE driver should help resolve the issue. In the event the issue does not get resolved, please edit the item and set the state to Active.

Thanks,
Pooja Harjani
Program Manager
Posted by karlodegrano on 9/29/2010 at 3:01 AM
This will solve all your headaches. Enjoy!

Download and install 64 bit driver
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

Check this link for Non-Sysadmin access
http://www.eggheadcafe.com/software/aspnet/28910398/opendatasource-authority-on-sql-server-2005.aspx

Just use the "Excel 12.0" for the extended properties

Sample Query:

SELECT * INTO #tempImportHLR FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\path\file.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
select * from #tempImportHLR
drop table #tempImportHLR