HiI am just this minute learning about querying data in an excel sheet from sql query. I have picked up instructions from people doing similar exercises and have tried what I think is the correct syntax and I think I have the correct settings too. I am on a local database and seem to have the Microsoft.ACE.OLEDB.12.0 linked server that is recommended to use.I have also tried running the following commands and also restarting SQL Server 2008 to no avail.USE [master] GO sp_configure 'show advanced options',1 GO reconfigure with override GO sp_configure 'Ad Hoc Distributed Queries',1 GO reconfigure with override 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
This is the query I am using:SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\extract1.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]') Basically I am new to this and still wondering what some of the syntax means so some things in my query are assumptions, like I do not know what HDR=YES means for example but have left that as is copied from examples I have found.Current error I have is:quote:
Msg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Can't Fix OpenrowSet Error
Posted - 2011-09-05 : 05:27:25
