We recently upgraded our Toad Data Point to version 4.3 (32 bit).
Following the upgrade of TDP 4.0 to TDP 4.3 (32 bit), I have encountered the following issue when running automations on my desktop.
The automation uses a single Export Wizard. The Export Wizard includes
a query to extract data,
the resulting data is sent to a .xlsm (macro enabled) excel spreadsheet,
a single macro is kicked off in the spreadsheet.
When this automation is run on my desktop, it deletes the macro enabled spreadsheet. I then had this spreadsheet restored, and have tried to run the automation again with the same results (I now have a backup copy of my own so that I can test this automation). I have tried another automation with the same result. These automations worked fine prior to the upgrade to TDP 4.3.
The other puzzling thing is that when I remove the macro from the automation (the remaining operations include running a query and saving the data to the macro enabled spreadsheet), the automation runs normally and sends the data to the spreadsheet with no issues. Only when I include the instructions to kick off a macro within the automation, does the automation delete the excel spreadsheet. I have even revised the macro code within the spreadsheet to remove all vba functionality (only the macro begin and end statements remain), and the spreadsheet is still deleted.
This issue is of great concern as we cannot continue to allow the automation process to delete our spreadsheets.
The other extremely puzzling thing is that all of our automations that are published to run on TIC (version 4.3) have run normally with no issues. The issue only seems to occur when the automation is opened on my desktop and is run from my desktop.
The actual error message involves the file being used by another process, but results in the excel spreadsheet being deleted. The text of the automation notes are shown below:
– 2/7/2019 4:55:38 PM: Funding total today2.log: Setting up environment
– 2/7/2019 4:55:38 PM: Funding total today2.log: Script run by Toad Data Point 4.3.0.718 as jraguse on FFC-DESKTOP66
– 2/7/2019 4:55:38 PM: Funding total today2.log: Build started
– 2/7/2019 4:55:38 PM: Funding total today2.log: Compiling script
– 2/7/2019 4:55:38 PM: Funding total today2.log: Start CompileWorkflow
– 2/7/2019 4:55:38 PM: Funding total today2.log: Before Compile
– 2/7/2019 4:55:38 PM: Funding total today2.log: Return from CompileWorkflow
– 2/7/2019 4:55:38 PM: Funding total today2.log: Workflow Compiled without Errors
– 2/7/2019 4:55:38 PM: Funding total today2.log: Workflow Compiled has warnings
– 2/7/2019 4:55:38 PM: Funding total today2.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.
– 2/7/2019 4:55:38 PM: Funding total today2.log: Build completed
– 2/7/2019 4:55:38 PM: Funding total today2.log: Create Workflow instance
– 2/7/2019 4:55:38 PM: Funding total today2.log: Begin execution script activities
– 2/7/2019 4:55:38 PM: Funding total today2.log: Variable “ROOT_PATH” set to
– 2/7/2019 4:55:38 PM: Funding total today2.log: Started workflow instance
– 2/7/2019 4:55:38 PM: Funding total today2.log: Scanning the script for prompt bind variables
– 2/7/2019 4:55:38 PM: Funding total today2.log: Connection description = [connection info removed]
– 2/7/2019 4:55:38 PM: Funding total today2.log: Connection node found
– 2/7/2019 4:55:39 PM: Funding total today2.log: Connection to: [connection info removed]
– 2/7/2019 4:55:41 PM: Funding total today2.log: Running embedded export template
– 2/7/2019 4:55:41 PM: Funding total today2.log: Executing export using connection [connection info removed] is not XQuery Connection
– 2/7/2019 4:55:48 PM: Funding total today2.log: Export_1 - The process cannot access the file ‘S:\Automation\Daily Funding Total\Funding Totals.xlsm’ because it is being used by another process.
– 2/7/2019 4:55:49 PM: Funding total today2.log: Failed
I tried to reproduce the issue of deleting the *xlsm file. I can't find any issues. Seems like we need to look closer at how your export wizard file is created, the rest of the automation script and the xlsm file itself. Can you open up a support ticket? We can go into more detail from there.
Note: I see you are using a substituation drive. Does this map to shared network drive? If so, in this case we copy the xlsm file locally before exporting and then copy back. This process of copying may be taking a long time and be the cause of the "File in use" error. I believe we made some improvements to this in TDP 5.0. This version is not yet released but is available through Beta and is worth a quick try. https://forums.toadworld.com/t/toad-data-point-5-0-beta-program/41277
I have also just tested the automation in the beta version of TDP 5.0.
With TDP 5.0, the automation attempts to create a spreadsheet with the date and time stamp appended to the name (however, I did not check the box to have date / time appended to the name). Then the new date/time spreadsheet is deleted, and the error message again talks about the file being used by another process. The original spreadsheet (the one listed in the automation) is not touched in the automation process.
Here is the text of the automation log:
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Setting up environment
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Script run by Toad Data Point 5.0.1.12 as jraguse on FFC-DESKTOP66
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: AppDataDir = C:\Users\jraguse\AppData\Roaming\Quest Software\Toad Data
Point Beta 5.0
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Temp Dir = C:\Users\jraguse\AppData\Local\Temp
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Build started
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Compiling script
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Start CompileWorkflow
– 2/12/2019 8:43:33 AM: TestTDP5-0.log: Before Compile
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Return from CompileWorkflow
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Workflow Compiled without Errors
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Workflow Compiled has warnings
– 2/12/2019 8:43:34 AM: TestTDP5-0.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.
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Build completed
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Create Workflow instance
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Started workflow instance
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Begin execution script activities
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Variable “ROOT_PATH” set to
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Variable “RUN_MODE” set to Test
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Scanning the script for prompt bind variables
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Connection description = [connection info removed]
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Connection node found
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Connection to: [connection info removed]
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Running export template \ffc-apps\Shared\Accounting\Jeff\Test TDP5-
0.txp
– 2/12/2019 8:43:34 AM: TestTDP5-0.log: Executing export using connection [connection info removed] is not
XQuery Connection
– 2/12/2019 8:43:50 AM: TestTDP5-0.log: Export_1 - The process cannot access the file 'S:\Accounting\Jeff\Test
TDP4-32019-02-12 08-43-34.xlsm’ because it is being used by another process.
– 2/12/2019 8:43:50 AM: TestTDP5-0.log: The process cannot access the file 'S:\Accounting\Jeff\Test TDP4-32019
-02-12 08-43-34.xlsm’ because it is being used by another process.
– 2/12/2019 8:43:50 AM: TestTDP5-0.log: Failed
So it looks like the issue only occurs when automation script and xlsm file are in same remote dir. This has been fixed in QAT-14201 but missed the Beta. Can you use this work around for now?
Yes - it looks like you have isolated the problem.
Is there any release after 4.0 that should work? We can use the work-around for now, but might consider a different version if there was another option. We needed to install 4.3 to pick up an updated connection for SQL Server, so going back to 4.0 is not an option, but perhaps there is some other version that would work. Otherwise we will wait for the next release that includes QAT-14201.