Unanswered: Importing from excel, but must open the file in excel first?
While migrating a 32 bit sql server 2000 db to sql server 2008r2 64bit, I've found a strange issue while importing from an excel file: the import fails with Msg 7399 unless I manually open the file in Excel before the import. I'm not changing or saving the file. Just opening and closing it without saving. Afterwards, the import works fine. If I make a copy of the problem sheet before opening, then open the original in Excel and close, I can import the original that I opened in Excel, but not the copy that wasn't.
I'm reluctant to inform the uploading client that their files are haunted, especially since this was working fine on the 32 bit server. The only thing that has changed is that I've changed the data provider form the 32 bit JET to the 64 bit ACE. In the 32 bit environment, I can import that previously unopenable copy without a problem. I'm not sure if it's related, but the Jet import is also 5 times faster than the 64 bit ace import (after I've opened & closed the file in excel). One other detail: after I've imported in the 32 bit environment, I can then import in the 64 bit environment without an issue - even though the file hasn't changed.
Has anyone heard of such a strange glitch, and is there a workaround that doesn't require manually opening the file?
Here is an example of the statement that doesn't work until I've opened & closed the file in Excel:
('Microsoft.ACE.OLEDB.12.0','Data Source="\\server\sites\incoming\problemFile - Copy.xls";
Extended Properties="Excel 8.0;HDR=No;IMEX=1"'
)...['First Sheet$'] x
Last edited by onansalad; 02-18-16 at 10:45.
Reason: additional detail
Additonal info: I've compared the file before opening in excel and after opening in excel with a hex editor, and it appears that excel is changing text in the first line @ position 559 from "Java Excel APIv2.5.7" to "Kit Lemmondsv2.5.7" which is the name of the workgroup user registered in Excel. I suspect that this position is used by excel to tell if the file is exclusively opened by another user. I'm unsure why the 32 bit JET provider had no problem with the original unopened file but the 64 bit ACE provider does.