SQL Server 2005 into Excel with OPENROWSET MSG 7399/7303

Question

My patience is at an end with this issue. I run jobs via SSMS or the agent that have been working perfectly fine up until last week that insert data from tables/views into various Excel files.

Most recently, the jobs/code just keep running while never finishing...these jobs use to only take up-to 15-25 seconds to complete but now never end. I've tested the code below on a new sheet and it goes instantaneously! The error I'm getting also doesn't help, the solutions I've found in Google were no help or were already set.

Any ideas what would cause a openrowset export to just bomb out and not do its job?

Code I generally run that works:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;Database=C:\Automation\DYNAMIC\Output\File.xlsx;',
'SELECT * FROM [Data$]')

SELECT *
FROM [REPORTING].[dbo].[tbl_table]

The current error message:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

System:

SQL Server 2005 32-Bit on Windows XP 32-Bit

Updated/Edited information:

Excel sheets contain either functions on a separate tab or Charts extrapolating the data from the [Data$] tab (if any of this helps)
I have created a brand new worksheet and tested my insert query and it worked perfectly
The source file was not originally created on the server, but instead transferred via Share Drive (I don't think this would matter though)

Any help is appreciated!!

Thanks

You may have done all this, but: 1. Do the filenames that fail contain space characters? 2. Try renaming a failing file, create a "new sheet" with the same path and name as the failure, and INSERT. Does that work? (If so, the contents of the file is the likely problem.) 3. Does this work with explicit column name lists, not SELECT *? 4. If you "Save As.." one of the failing Excel files under a new name, can you insert into it? Have you opened the Excel files that fail and checked the Data$ range very carefully (then closed it of course, before querying)? 6. Does SELECT from OPENROWSET work? — Steve Kass
@Steve - Thanks for responding. 1) No, it has underscores to represent spaces. 2) Does not work 3) No 4) No and yes 6) No, it just executes forever. I have however created a brand new test.xlsx with [Sheet1$] as the destination, inserted the same data i wanted for the other sheet and it worked instantly! I'll append the above question but also place here - does it have anything to do with me having a few tabs with Charts in it? I also have the same issue with another file (different directory) that has no charts, just some functions. That one for sure has worked before. At a lost here — Darundo
More guessing: To verify that SQL Server can even "see" the problem file, does this return a result? select * from openrowset(BULK N'C:\Automation\DYNAMIC\Output\File.xlsx',single_blob) as T I'm still fuzzy on what does and doesn't work. To #2 earlier, you said renaming the bad file and creating a new one (but I was unclear with "sheet" vs. "file") does NOT work. Is the following right (If not, can you clarify what failed in #2 before)? a) You can insert into Sheet1$ of a brand new C:\Automation\DYNAMIC\Output\test.xlsx, but b) You canNOT insert into a brand new file with the problem name. — Steve Kass
Yes a blob result is returned with BULK. #2) I can insert into a brand new file that has a different file name. I cannot insert into the file with the same name but as a new workbook. And I cannot insert into a new Sheet of the original bad workbook. I guess SQL is blocking these files for whatever reason? It's interesting to say the least...but a pain in the... — Darundo
It sounds like the entire problem is with the file name, then. From what you've said, you have no problem using a different file name and you always have problems with the existing file name (regardless of whether the .xlsx file is new or old). Since BULK returns data, it sounds like a strange bug with the ACE driver. Maybe the underscores in the file names are a problem? The solution is to use a different naming convention for the Excel files. Hopefully that's an option for you. — Steve Kass

Aleli Aleli · Accepted Answer · 2012-05-16T00:40:07

I have had this same issue and what seemed to correct it was restarting SQLServer Service

SQL Server 2005 into Excel with OPENROWSET MSG 7399/7303

1 Answers