February 19, 2010 at 11:21 am
Greetings all. I am using OPENROWSET to insert a sheet from an Excel file into a staging table as follows.
SELECT
*
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;Database=E:\FTTPVA_SHARED\UPLOAD_FORMS\ZZMB52\zmmr41.xls',
'select * from [mysavedreport$]'
)
WHERE CONVERT(INT,[material group]) IN (30000001,30000002)
The Excel file is generated from a report on one of our websites, where the user hits a button on the page and a dialog box pops up asking if you want to open it or save it. Note that in the above code, the name of the sheet is 'mysavedreport'. This is a default name given to the sheet when the user chooses to open the file first, then saves it to our server folder by using the file menu from within Excel, and giving the file the name 'zmmr41.xls'. The size of this file is 416 kb. However, if the user chooses to save the file from the windows dialog box rather than opening it first, the same save as dialog box opens up, and the user saves the file in the same server folder, with the same name. However, when the file is saved with this method, the name of the sheet becomes the name of the workbook. I thought "no problem", I'll just change the code and have the users always save without opening it. However, even when I change the code to reflect the new sheet name, I 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.].
Now, the size of this file is just over 700 kb, but except for the sheet name and size, they appear identical. I can even copy the sheet from the directly saved workbook into a new workbook, and it works no problem. I guess my question is, has anyone else experienced this, and if so, how can I fix it. Is this maybe a question better posted in one of the Excel forums? I'm completely flumoxed.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 19, 2010 at 11:32 am
Alright, I think I'm on to something. If I open the larger of the two files, the one that was saved to the server folder directly from the save as dialog box that pops up when you hit the button on the web page, then choose 'save as' from the file menu, the default file type is 'Web Page (*.htm; *.html). However, when I open the smaller of the two files, the one that was saved to the server folder using the file menu from within Excel, then choose 'save as' from the file menu, the default file type in the save as dialog box is 'Microsoft Office Excel Workbook (*.xls)'. So, is it possible to make OPENROWSET recognize the former file? I apologize if I'm not explaining this clearly.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply