An interesting request came up from one of our customers. They were unable to complete a process that was running until lately. During a mass update in a database from a Microsoft Excel file, the export process was not producing a file.
This custom upload process is based on a Microsoft. ACE.OLEDB.12.0 SQL Server Linked Server Provider which provides SQL the ability to directly read Excel files.
The third-party vendor who had deployed the Provider was kind enough to also provide the below screenshot, in order to troubleshoot the issue.
The query used to reproduce the issue was: SELECT * INTO IMP_CSIItemTEST FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;HDR=YES; Database=''E:\Excel user files\test.xlsx''','[ItemsList$]') The message received as a result was: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".
As the customer was certain that no changes had occurred in OS or SQL level, the most appropriate decision to proceed with the investigation was to execute a Process Monitor session, in order to identify what was going on in the background.
By doing that, we managed to identify that the sqlservr.exe process was attempting to create a file but access was denied, so obviously it was a matter of permissions.
In order to overlap the issue, full permissions on the volume were provided to the SQL Server Service Account and a second try was attempted. This time, everything went smoothly and the process completed successfully.
Comments