January 29, 2013 at 2:39 am
hi folks,
i am tired to breaking my head in this simple task, trying to import an xlsx file to 2008 r2,
after doing lot of googling i have done these following:
1. UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.).
2. Install the access and other engines from following site
3. Ran the following query in sql server
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
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
4. I am using this query
SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,'select * from [sheet1$]‘)
after doing all this i was getting 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)"."
after doing some google search i got a link (http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx) and follow the instruction
but no luck still getting the same error.
can any one came across this situation, please help.
January 31, 2013 at 5:10 am
hello,
On SQL 2k8 express,
I followed your steps of
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
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
then used this SQL which worked perfectly,
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\temp\book1.xlsx;HDR=no', 'Select * from [Sheet1$]')
only difference is that I had HDR=no
or yes if you have header lines in your excel file.
Dave
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply