"The process cannot access the file ..." Automation Error

jemery · January 10, 2017, 8:34am

Hello Toadies.

I am having issues when trying to run an automation using Toad Data Point 4.0.1.775

The automation should be quite simple: A short SQL query to be exported as an Excel file, where a VBA script then sends the formatted results as an email. However, when I try to run the automation, I receive the following error:

**
“Export_1 - The process cannot access the file 'C:\Users\myname\AppData\Roaming\Quest Software\Toad Data Point 4.0\filename.xlsm because it is being used by another process.”**

I see that for some reason the file that is to be exported is being created in two places - the folder I designated in the automation, and also in the AppData/Roaming folder. I assume the latter is some sort of temporary file that is supposed to be deleted, but it is not, which causes the issue.

Interestingly, although Toad tells me that the export failed, the query is run, I get my data in an Excel file, and the VBA works properly and the email is sent. Furthermore, when discussing this with my two colleagues, the automation works flawlessly on their computers, which leads me to believe that the issue may be on my local computer.

I have viewed this thread: http://www.toadworld.com/products/toad-data-point/f/36/t/7151 but the suggestions provided don’t seem to help me with my issues.

Below I have pasted the verbose log transcript from the automation:

– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Setting up environment
– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Script run by Toad Data Point 4.0.1.775 as jemery on FFC-DESKTOP56
– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Build started
– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Compiling script
– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Start CompileWorkflow
– 1/10/2017 10:23:45 AM: Daily New Dealers Report.log: Before Compile
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Return from CompileWorkflow
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Workflow Compiled without Errors
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Workflow Compiled has warnings
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Build completed
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Create Workflow instance
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Started workflow instance
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Begin execution script activities
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Variable “ROOT_PATH” set to ‘’
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Running export template S:\Automation\Daily New Dealers\Daily New Dealers Report.txp
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Executing export using connection CompassDB2 (FNDUSER), KWDB104 is not XQuery Connection
– 1/10/2017 10:23:47 AM: Daily New Dealers Report.log: Beginning export: <Quest.Toad.ImportExport.ExportEngine> '' AND SUBQUERY1.PDDCHAR <> '' AND DATE(SUBQUERY1.PDDCHAR) = CURRENT_DATE - 1 DAY GROUP BY KWPDLOC.PDNO, KWPDBASE.PDNAME, KWPDLOC.PDADR1, KWPDLOC.PDCITY, KWPDLOC.PDST, KWPDLOC.PDZIP, SUBQUERY1.PDDCHAR ORDER BY KWPDLOC.PDNO]]></Quest.Toad.ImportExport.ExportEngine>
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Adding Export Notification Subscriber {0}
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Export Started [1/10/2017 10:23:47 AM]
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) ** START **
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Export using connection: CompassDB2 (FNDUSER), KWDB104
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Export Wizard: Building object list
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Build List done.
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Exporting Data (1 of 1)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) BEGIN Query :
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:47 AM Thread (12) Object SQL Query started.
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (6) InternalReadBackground - after ExecuteReader - command SELECT KWPDLOC.PDNO AS DEALER_NUMBER ,KWPDBASE.PDNAME AS DEALER_NAME ,KWPDLOC.PDADR1 AS ADDRESS ,KWPDLOC.PDCITY AS CITY ,KWPDLOC.PDST AS STATE ,KWPDLOC.PDZIP AS ZIP ,CASE WHEN SUBQUERY1.PDDCHAR IS NULL THEN ‘01/01/1900’ ELSE SUBQUERY1.PDDCHAR END AS APPROVAL_DATE FROM KWDB104.KWPDLOC KWPDLOC LEFT JOIN (SELECT KWPDDATA.PDDCODE ,KWPDDATA.PDDCHAR ,KWPDDATA.PDNO FROM KWDB104.KWPDDATA KWPDDATA WHERE KWPDDATA.PDDCODE LIKE (’%APPRD%’) AND KWPDDATA.PDDCODE NOT LIKE (’%APPRDAYS%’)) SUBQUERY1 ON SUBQUERY1.PDNO = KWPDLOC.PDNO LEFT JOIN KWDB104.KWPDBASE KWPDBASE ON KWPDBASE.PDNO = KWPDLOC.PDNO WHERE KWPDLOC.PDLOC = 0 AND KWPDLOC.PDADR1 <> ‘’ AND SUBQUERY1.PDDCHAR <> ‘’ AND DATE(SUBQUERY1.PDDCHAR) = CURRENT_DATE - 1 DAY GROUP BY KWPDLOC.PDNO, KWPDBASE.PDNAME, KWPDLOC.PDADR1, KWPDLOC.PDCITY, KWPDLOC.PDST, KWPDLOC.PDZIP, SUBQUERY1.PDDCHAR ORDER BY KWPDLOC.PDNO
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (4) Excel version = 16
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) DoneReadTreadWriter
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) Writing rows 0 - 1
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) Writing rows Finalize
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) Executing macros from Excel.
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) Loading Excel Assemblies
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:48 AM Thread (13) Trying Excel 2010
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:50 AM Thread (13) Deleting existing temp file: C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm…
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:50 AM Thread (13) Copying macro file from \ffc-apps\Shared\Automation\Daily New Dealers\Daily New Dealers Report.xlsm to C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm…
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:50 AM Thread (13) Checking if copied file exists: True
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:50 AM Thread (13) Opening file C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm…
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:50 AM Thread (13) Running macro, Mail_Selection_Range
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) Saving workbook.
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) Deleting existing template file: \ffc-apps\Shared\Automation\Daily New Dealers\Daily New Dealers Report.xlsm…
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) Copying macro file from C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm to \ffc-apps\Shared\Automation\Daily New Dealers\Daily New Dealers Report.xlsm…
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) Checking if copied file exists: True
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) User cancelled
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (13) DoneReadThreadWriter finally - adapter.RowsRead 1
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (12) END Query :
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (12) Object SQL Query finished with error: The process cannot access the file ‘C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm’ because it is being used by another process.
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (12) at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: at System.IO.File.InternalDelete(String path, Boolean checkHost)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: at System.IO.File.Delete(String path)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: at Quest.Toad.ImportExport.Writers.WriterExcelInstance.ExecuteMacro(String macroNames, String filename)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: at Quest.Toad.ImportExport.Writers.WriterExcel.WriteFooter(Object tableObject)
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: at Quest.Toad.ImportExport.ExportObjectData.DoneReadThreadWriter()
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (12) Export Finished [1/10/2017 10:23:52 AM]
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: Thread (12) Build Time 00:00:00.4490000
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: Thread (12) Complete Time 00:00:05.0590000
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: 10:23:52 AM Thread (12) ** END **
– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: Export_1 - The process cannot access the file ‘C:\Users\jemery\AppData\Roaming\Quest Software\Toad Data Point 4.0\Daily New Dealers Report.xlsm’ because it is being used by another process.

– 1/10/2017 10:23:52 AM: Daily New Dealers Report.log: Failed

If anybody can help me with this, I would be greatly thankful.

John

Martin.Holkovic · January 11, 2017, 5:43am

Hello jemery,

Sorry for your troubles.

Since 4.0 we’ve added quite a few changes related to macro execution in an attempt to fix unexpected hanging. We were able to fix some of the hang cases by simply copying the remote .xlsm file to a temp local file path and then executing macros. From the log you’ve pasted i can already see exactly where the problem occurred. The moment when the error happened is once macro is successfully finished executing and the .xlsm file is was successfully copied back to its original source folder and right after that point there is an attempt to delete the “temp xlsm file path” but its, for some reason, blocked “by another process”. This could explain why you are able to perceive your script as successfully finished, because in fact this was the last step to be executed… I suspect that the “another process” is not properly closed or still-closing Excel application so we might implement a change to only “try delete the file” and if impossible to do so then leave it be as it will be deleted/overwritten by the next execution anyway.

We will attempt to fix it for the next version so until then I’d advise you to use a workaround - add few lines of VBA code to your macro that at the end of execution do:

ThisWorkbook.SaveAs “remote_path”

Kill “temp_path”

This way your macro ensures the file is deleted so the “file-delete” code portion of Toad is skipped because it’s not trying to delete it if it doesn’t exist anymore.

Hope this helps,

Martin

jemery · January 11, 2017, 6:58am

Martin,

You are a miracle worker. The VBA code worked like a charm, and now I can publish automations again.

Thank you,

John