March 1, 2011 at 4:01 pm
Hi all,
As JET.OLEDB does not work for 64 bit servers, I have to change to ACE provider in order to be able to upgrade to x64.
I'm currently using OPENROWSET for both importing and exporting programmatically from and to Excel. I have this queries:
IF (@Excel_12 = 0)
BEGIN
SET @sql_export = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @file + ';'', ''SELECT * FROM [' + @sheet + '$]'') (' + @columns + ') ' + @query
END
ELSE
BEGIN
SET @sql_export = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + @file + ';'', ''SELECT * FROM [' + @sheet + '$]'') (' + @columns + ') ' + @query
END
--PRINT @sql_export
EXEC sp_sqlexec @sql_export
And the JET part works perfectly... But the ACE part doesn't...
I've tried doing this:
1. Installed the 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work
2.
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
And restarting the service, the server..... but I haven't had luck....
Colud you help me please!
What else should I install or configure to make this work. I'm using SQL Server 2008 R2 on Windows server 2007 R2 and Microsift Office 2007.
Regards,
Victor
March 2, 2011 at 7:08 am
I don't know who told you, but ACE also doesn't work on 64 bit.
http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/58c4c61e-fa86-4809-bf7d-21bacb055d3e/
(sorry I ruined your day :-))
edit: apparently there is a 64-bit release for office 2010:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2011 at 9:58 am
Is there a particular error that you're getting when you try the ACE provider?
March 4, 2011 at 1:13 pm
The error I'm getting is the good old:
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)".
"
Thanks a lot
March 4, 2011 at 1:15 pm
Hi, yes, I'm using the 2010 version... but still the error is showing up
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply