Recently a customer raised a case to us. They need to import a lot of DBF files into SQL SERVER. But they found when DBF fine name is longer than 8 characters they received below error:
SELECT * FROM OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','dBase 5.0;HDR=YES;DATABASE=F:\temp\',
'SELECT * FROM SJSMX00103.DBF')
OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'SJSMX00103.DBF'. Make sure the object exists and that you spell its name and the path name correctly. If 'SJSMX00103.DBF' is not a local object, check your network connection or contact the server administrator.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".
We are quite sure that SJSMX00103.DBF is under ‘F:\temp\’. Therefore, we captured process monitor trace and found SQL SERVER was looking for ‘F:\temp\SJSMX001.DBF’ instead of ‘F:\temp\SJSMX00103.DBF’
We made a research for this. And even captured TTT to confirm the file name was not truncated to 8 characters before calling ‘MICROSOFT.ACE.OLEDB.12.0' driver.
It seems ‘MICROSOFT.ACE.OLEDB.12.0' and 'MICROSOFT.ACE.OLEDB.16.0' drivers which are now available to download have 8.3 file name limitation.
We consulted office team. Actually, new ‘MICROSOFT.ACE.OLEDB.12/16.0' drivers which can support 64 characters file name are now available in Office365 Build 1807 and later version.
We tried to uninstall old ‘MICROSOFT.ACE.OLEDB.12.0' and ‘MICROSOFT.ACE.OLEDB.16.0' drivers. Install latest Office365. New providers appear in SQL SERVER. Now we are able to read/write long file name DBF files