OPENROWSET And Excel Problems

Create an excel sheet
In the first 2 rows put some data, save the excel sheet as testing.xls on the c drive
Execute the command below
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')

You will see 1 row since the first row will be used for header names
If you want 2 rows you need to add HDR=No like this
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')


Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=SS:\testing.xls','SELECT * FROM [Sheet1$]')

The path can't be found (SS) you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].


Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing2.xls','SELECT * FROM [Sheet1$]')

When you spell the filename (testing2) wrong you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet11$]')

When you spell the sheet name (Sheet11) wrong you get this error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [Sheet11$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [P2 2003 DJIA updates$]'].

Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls','SELECT * FROM [Sheet1$]')

When you type the wrong path (D) but the path exists, then you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

So hopefully the next time you get an error you can quickly figure out if it's the file name, sheet name or path name that is wrong