1) Because you are using the
'Microsoft.ACE.OLEDB.12.0 Provider, Install Microsoft Access Database Engine 2010 Redistributable if you haven't already done so.
If you have SQL Server 64 bit then download 64bit version else 32bit version.
If you install incorrect version you will get the following error:
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)".
You can not install both versions.
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
3) use OPENROWSET by also supplying the query to perform within the excel. Each Excel sheet is treated as a table, whose table name = sheetname followed by $.
If first row in excel contains name of columns then use HDR=YES
SELECT * FROM OPENROWSET (
'SELECT * FROM [Sheet1$]'
MCTS, MCITP - Please mark posts as answered where appropriate.
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.