June 18, 2008 at 1:10 am
I use the following code to import data from an excel source:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;IMEX=1;Database=d:\import.xlsx;', 'SELECT * FROM [Sheet1$]');
I then get the following error code:
[highlight]Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".[/highlight]
I have installed AccessDatabaseEngine.exe, and the provider is available when I check with an empty .udl file. This works fine on my development machine (v2008 server + sql 2005) but not on my production environment (w2003 64bit with sql 2005 64bit)
Does anyone have any idea what I can do to fix this?
June 19, 2008 at 6:20 am
Have you created a linked server? Firstly, you need create a linked server. After that, you must use the linked server in openrowset function.
June 19, 2008 at 10:24 am
Dumb question: where is the excel spreadsheet? Remember, the drive reference "d:\import.xlsx" is relative to the server, not to the computer where you're running the query.
Steve G.
June 19, 2008 at 11:44 pm
aureolin (6/19/2008)
Dumb question: where is the excel spreadsheet? Remember, the drive reference "d:\import.xlsx" is relative to the server, not to the computer where you're running the query.Steve G.
It's not a dumb question, although in this case the excel sheet is named import.xlsx and is lying on the D:\ drive in my production environment.
The problem seems to be that the office drivers are 32bit only, while openrowset only works with 64bit ole db drivers 🙁 I have been totaly unable to find a way for this to work.
As a workaround I made a SSIS package doing the same job as the openrowset function, then I call that from my stored procedure, using the 32 bit dtexec found under c:\program files(x86)\ folder. I'm not very happy with this workaround, but it works.
Anyone know how to call a 32 oledb driver from 64bit SQL server would be very welcome.
edit: oh, and as for linked server, this have the same problem as openrowset, and there is no ace driver available in the dropdown when i try to create the linked server.
June 20, 2008 at 10:15 am
I think the "linked server" message is a bit of a red herring. SQL Server is simply trying to tell you that it can't connect to the 'remote' data source. I could easily see someone spending hours messing with the linked server settings when they only have a file missing, or as in your case, a file not accessible because of 32/64 bit crossover problems.
Steve G.
August 16, 2010 at 6:12 pm
I had this exact same error. Try using 'OpenDataSource' instead of OpenRowSet?
It's going to be slower though!
Make sure you specify ';Extended properties =Excel5.0'
or
'Extended properties =Excel 8.0'
based on your needs, in the provider string, in addition to the source file
You should also have downloaded the 64-bit version of Office 2010
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply