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.].
When you have an excel file open and you are trying to open it with OPENROWSET 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.].
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
