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

Excel 64 bit driver Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 1:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
Looks like someone got it to work. Here's the link.
I had similar issues trying to link an Access database to the sql server.

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407452
Posted Tuesday, January 15, 2013 2:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
I've been in on a few other ACE /EXCEl threads, and have my comments saved from when i thouroughly tested this and got it to work:
myself(Lowell)

a couple of prerequisites:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
After that is isntalled:
--Required settings for the provider to work correctly as a linked server
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

and then the code for the linked server:
--#################################################################################################
--Linked server Syntax for Excel with ACE 64 driver
--#################################################################################################
DECLARE
@srvproduct nvarchar(256),
@provider nvarchar(256),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an excel spreadsheet as a linked server.
SET @server = 'MyExcelACE'
SET @srvproduct = ''
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
set @provstr = 'Excel 12.0'
SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--what spreadsheets (table equivilents are available?
EXEC sp_tables_ex 'MyExcelACE'
--you MUST know the name of the spreadsheet;
--spreadsheet name has a dollar sign at the end of it!
--I've personally never gor a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
select * from MyExcelACE...ActivityData$;
select * from MyExcelACE...Sheet1$;



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1407497
Posted Tuesday, January 15, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
That's great stuff Lowell, I didn't know you could force the installation of the provider.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1407501
Posted Tuesday, January 15, 2013 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,962, Visits: 32,498
Koen Verbeeck (1/15/2013)
That's great stuff Lowell, I didn't know you could force the installation of the provider.

Thanks Koen!
I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1407503
Posted Tuesday, April 23, 2013 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
Lowell (1/15/2013)
Koen Verbeeck (1/15/2013)
That's great stuff Lowell, I didn't know you could force the installation of the provider.

Thanks Koen!
I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.


I found this solution so great I made a blog post out of it.
Force installation of 64-bit ACE OLE DB provider

Lowell, I gave you credit for this solution in the blog post, however I did not found a blog/website/linkedin/whatever of you where I could point to.
If you want me to link to something of you to give proper credit, let me know.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1445374
Posted Thursday, April 25, 2013 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 10:58 AM
Points: 6, Visits: 210
Kudos to Lowell for revealing the secret to using the command line to force installation of the 64-bit ACE driver on a machine with 32-bit Office apps. I ran into that wall a couple weeks ago and gave up fighting it - Lowell's solution solved my problem, so I can now run openrowset queries against Excel files. Thanks!
Post #1446587
Posted Friday, February 21, 2014 6:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 12:10 PM
Points: 1,341, Visits: 558
Where can i check which driver is installed on a particular machine?

Thanks
Martin
Post #1543953
Posted Wednesday, May 7, 2014 9:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
An old thread to be sure, but I'm trying to get this to work now with Office 2013.

I found there's a new Access Database Engine for Office 2013.
http://www.microsoft.com/en-us/download/details.aspx?id=39358

If I have the time (and remember) I'll post back once I get this working again.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1568752
Posted Thursday, May 8, 2014 12:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
Now here's a bit of a sticky wicket. I installed the new 2013 Access Run Time engine and my OPENROWSET query still wasn't working.

Got to poking around and found that the Microsoft.ACE.OLEDB.12.0 provider is not in my SQL 2008 Providers list but it is in the Providers for SQL 2012 (I have 2 versions of SQL Server co-existing on this machine).

Now I need to figure out how to get it in both places. I'm hoping there's a secret parameter or something on this new Access Engine installer (like /passive as Lowell found), but so far I've had no luck finding it.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1568770
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse