Automation Select to File Error

sienm · April 24, 2019, 4:05pm

I am using the select to file automation in Toad and the automation is failing every day with this error message:
The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190422_094306.csv' because it is being used by another process

Some additional information:

It doesn't give this error when I manually run the .tas file in Toad, the file is successfully created.
It creates the file then fails during export when I run it in task scheduler (I see the file in the folder, but it is empty).
When I try to export the file as .xlsx it works but fails with the aforementioned error when exporting a .csv file

The automation creates this file with a time mark down to the second, so I don't know what other process could be using this file! Any help would be greatly appreciated.

Bellow is the automation log
-- 4/22/2019 9:42:54 AM: AWV_for_2019.log: Setting up environment
-- 4/22/2019 9:42:54 AM: AWV_for_2019.log: Script run by Toad Data Point 4.3.0.718 as cn165894 on CNCVDA1819
-- 4/22/2019 9:42:54 AM: AWV_for_2019.log: Build started
-- 4/22/2019 9:42:54 AM: AWV_for_2019.log: Compiling script
-- 4/22/2019 9:42:55 AM: AWV_for_2019.log: Start CompileWorkflow
-- 4/22/2019 9:42:55 AM: AWV_for_2019.log: Before Compile
-- 4/22/2019 9:42:58 AM: AWV_for_2019.log: Return from CompileWorkflow
-- 4/22/2019 9:42:58 AM: AWV_for_2019.log: Workflow Compiled without Errors
-- 4/22/2019 9:42:58 AM: AWV_for_2019.log: Workflow Compiled has warnings
-- 4/22/2019 9:42:58 AM: AWV_for_2019.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.
-- 4/22/2019 9:42:58 AM: AWV_for_2019.log: Build completed
-- 4/22/2019 9:42:58 AM: AWV_for_2019.log: Create Workflow instance
-- 4/22/2019 9:42:59 AM: AWV_for_2019.log: Started workflow instance
-- 4/22/2019 9:42:59 AM: AWV_for_2019.log: Begin execution script activities
-- 4/22/2019 9:42:59 AM: AWV_for_2019.log: Variable "ROOT_PATH" set to
-- 4/22/2019 9:42:59 AM: AWV_for_2019.log: Scanning the script for prompt bind variables
-- 4/22/2019 9:43:00 AM: AWV_for_2019.log: Begin Export of AWV_2019
-- 4/22/2019 9:43:00 AM: AWV_for_2019.log: Connection description = cn165894@edwp (ETL_ACCESS_OWN)
-- 4/22/2019 9:43:00 AM: AWV_for_2019.log: Connection node found
-- 4/22/2019 9:43:05 AM: AWV_for_2019.log: Connection to: cn165894@edwp (ETL_ACCESS_OWN)
-- 4/22/2019 9:43:05 AM: AWV_for_2019.log: Connection successful
-- 4/22/2019 9:43:05 AM: AWV_for_2019.log: Connection logon: cn165894
-- 4/22/2019 9:43:06 AM: AWV_for_2019.log: Last schema: ETL_ACCESS_OWN
-- 4/22/2019 9:43:06 AM: AWV_for_2019.log: Default schema: ETL_ACCESS_OWN
-- 4/22/2019 9:43:06 AM: AWV_for_2019.log: Auto Commit: True
-- 4/22/2019 9:43:06 AM: AWV_for_2019.log: SQL to execute: CALL HP_OR_OWN.DROP_VOLTABLE('VT_TEMP');

CREATE VOLATILE TABLE VT_TEMP AS (

SEL DISTINCT
	  'Annual Wellness Visit or Physical Exam' AS measure
	, 'AWV' AS measure_id
	, 'STARS' AS "type"
	
	, CASE WHEN EXTRACT(YEAR FROM CAST(CAST(SERVICE.SERVICE_START_DATE_DIM_CK AS CHAR(8)) AS DATE FORMAT 'YYYYMMDD'))
			 = CAST(CAST(CURRENT_DATE AS DATE FORMAT 'YYYY') AS CHAR(4)) THEN 'Y' ELSE 'N' END AS compliant
	
	, CASE WHEN PROC.PROC_TYPE_CODE = 'C4' THEN 'CPT'
			 WHEN PROC.PROC_TYPE_CODE = 'HC' THEN 'HCPCS'
			 WHEN PROC.PROC_TYPE_CODE = '10' THEN 'ICD10CM' END || '-' || SERVICE.PROC_CODE_1 AS event_code
	, OREPLACE(PROC.PROC_DESC, ',', '') AS event_desc
	
	, CAST(CAST(SERVICE.SERVICE_START_DATE_DIM_CK - 19000000 AS DATE FORMAT 'mm/dd/yyyy') AS VARCHAR(10)) AS last_dos
	
	, ELIG_CURR.MEMBER_AMISYS_NBR AS mem_id

FROM HP_OR_OWN_TABLES.HNMA_PROSPECTIVE_PCP_MASTER AS ATTR_MEMS
INNER JOIN ETL_ACCESS_OWN.DIM_MEMBER_ELIG_CURR AS ELIG_CURR
	ON ELIG_CURR.MEMBER_AMISYS_NBR = ATTR_MEMS.MEMBER_AMISYS_NBR
	AND ELIG_CURR.PLAN_DIM_CK IN (83, 109) 
	AND ELIG_CURR.DELETED_IND = 'N'
	AND ELIG_CURR.ACTIVE_IND = 'Y'
LEFT JOIN FT_SERVICE_TRANSACTION AS SERVICE
	ON SERVICE.MEMBER_CURR_CK = ELIG_CURR.MEMBER_CURR_CK
	AND SERVICE.SERVICE_START_DATE_DIM_CK BETWEEN 20190101 AND 20191231
	AND (OREPLACE(SERVICE.PROC_CODE_1, '.', '') BETWEEN '99381' AND '99388'
		OR OREPLACE(SERVICE.PROC_CODE_1, '.', '') BETWEEN '99391' AND '99397'
		OR OREPLACE(SERVICE.PROC_CODE_1, '.', '') IN ('G0402', 'G0404', 'G0405', 'G0438', 'G0439'))
LEFT JOIN ETL_ACCESS_OWN.DIM_PROCEDURE AS PROC
	ON PROC.PROC_CODE_DIM_CK = SERVICE.PROC1_CODE_DIM_CK
	AND PROC.PLAN_DIM_CK = ELIG_CURR.PLAN_DIM_CK

)
WITH DATA
NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS;

SEL * FROM VT_TEMP ORDER BY 8;
-- 4/22/2019 9:43:07 AM: AWV_for_2019.log: ValidateSuffix: Using base suffix: DateTime('YYYYMMDD_HH24MISS')
-- 4/22/2019 9:43:07 AM: AWV_for_2019.log: ValidateSuffix: Substituted suffix: 20190422_094306
-- 4/22/2019 9:43:07 AM: AWV_for_2019.log: ValidateSuffix: Suffix doesn't seem to be a date value or variable token - Evaluating expression: 20190422_094306
-- 4/22/2019 9:43:07 AM: AWV_for_2019.log: ValidateSuffix: Error during expressin evaluation - returning: 20190422_094306
-- 4/22/2019 9:43:08 AM: AWV_for_2019.log: Export Template: <Quest.Toad.ImportExport.ExportEngine></Quest.Toad.ImportExport.ExportEngine>
-- 4/22/2019 9:43:15 AM: AWV_for_2019.log: 9:43:08 AM Thread (4) Adding Export Notification Subscriber {0}
-- 4/22/2019 9:43:15 AM: AWV_for_2019.log: 9:43:08 AM Thread (4) Export Started [4/22/2019 9:43:08 AM]
-- 4/22/2019 9:43:16 AM: AWV_for_2019.log: 9:43:08 AM Thread (4) ** START **
-- 4/22/2019 9:43:16 AM: AWV_for_2019.log: 9:43:08 AM Thread (4) Export using connection: cn165894@edwp (ETL_ACCESS_OWN)
-- 4/22/2019 9:43:16 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) Export Wizard: Building object list
-- 4/22/2019 9:43:16 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) Build List done.
-- 4/22/2019 9:43:17 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) Exporting Data (1 of 3)
-- 4/22/2019 9:43:17 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) BEGIN Query :
-- 4/22/2019 9:43:17 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) Object SQL Query started.
-- 4/22/2019 9:43:17 AM: AWV_for_2019.log: 9:43:12 AM Thread (4) Failed applying file suffix 20190422_094306 as date time format. Suffix it directly instead.
-- 4/22/2019 9:43:17 AM: AWV_for_2019.log: 9:43:14 AM Thread (15) InternalReadBackground - after ExecuteReader - command CALL HP_OR_OWN.DROP_VOLTABLE('VT_TEMP')
-- 4/22/2019 9:43:18 AM: AWV_for_2019.log: 9:43:14 AM Thread (6) DoneReadTreadWriter
-- 4/22/2019 9:43:18 AM: AWV_for_2019.log: 9:43:14 AM Thread (6) DoneReadThreadWriter finally - adapter.RowsRead 0
-- 4/22/2019 9:43:18 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) END Query :
-- 4/22/2019 9:43:19 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Object SQL Query finished.
-- 4/22/2019 9:43:19 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Exporting Data (2 of 3)
-- 4/22/2019 9:43:19 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) BEGIN Query :
-- 4/22/2019 9:43:19 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Object SQL Query started.
-- 4/22/2019 9:43:19 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Failed applying file suffix 20190422_094306 as date time format. Suffix it directly instead.
-- 4/22/2019 9:43:20 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) END Query :
-- 4/22/2019 9:43:20 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Object SQL Query finished with error: The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190422_094306.csv' because it is being used by another process.
-- 4/22/2019 9:43:20 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
-- 4/22/2019 9:43:20 AM: AWV_for_2019.log: at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
-- 4/22/2019 9:43:21 AM: AWV_for_2019.log: at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
-- 4/22/2019 9:43:21 AM: AWV_for_2019.log: at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost)
-- 4/22/2019 9:43:21 AM: AWV_for_2019.log: at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost)
-- 4/22/2019 9:43:21 AM: AWV_for_2019.log: at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding)
-- 4/22/2019 9:43:22 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.Writers.WriterBase.InitializeStream()
-- 4/22/2019 9:43:22 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.InitializeWriter(Object context)
-- 4/22/2019 9:43:22 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.StartBackgroundAdapter(Dictionary2 connections, Object context) -- 4/22/2019 9:43:22 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.Begin(LogEventHandler logEventHandler, Dictionary2 connections, Object context)
-- 4/22/2019 9:43:23 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Exporting Data (3 of 3)
-- 4/22/2019 9:43:23 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) BEGIN Query :
-- 4/22/2019 9:43:23 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Object SQL Query started.
-- 4/22/2019 9:43:23 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Failed applying file suffix 20190422_094306 as date time format. Suffix it directly instead.
-- 4/22/2019 9:43:24 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) END Query :
-- 4/22/2019 9:43:24 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Object SQL Query finished with error: The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190422_094306.csv' because it is being used by another process.
-- 4/22/2019 9:43:24 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
-- 4/22/2019 9:43:24 AM: AWV_for_2019.log: at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
-- 4/22/2019 9:43:25 AM: AWV_for_2019.log: at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
-- 4/22/2019 9:43:25 AM: AWV_for_2019.log: at System.IO.StreamWriter.CreateFile(String path, Boolean append, Boolean checkHost)
-- 4/22/2019 9:43:25 AM: AWV_for_2019.log: at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize, Boolean checkHost)
-- 4/22/2019 9:43:25 AM: AWV_for_2019.log: at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding)
-- 4/22/2019 9:43:26 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.Writers.WriterBase.InitializeStream()
-- 4/22/2019 9:43:26 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.InitializeWriter(Object context)
-- 4/22/2019 9:43:26 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.StartBackgroundAdapter(Dictionary2 connections, Object context) -- 4/22/2019 9:43:26 AM: AWV_for_2019.log: at Quest.Toad.ImportExport.ExportObjectData.Begin(LogEventHandler logEventHandler, Dictionary2 connections, Object context)
-- 4/22/2019 9:43:27 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Export Finished [4/22/2019 9:43:14 AM]
-- 4/22/2019 9:43:27 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) Export time = 00:00:06.7666158
-- 4/22/2019 9:43:27 AM: AWV_for_2019.log: Thread (4) Build Time 00:00:03.9876240
-- 4/22/2019 9:43:27 AM: AWV_for_2019.log: Thread (4) Complete Time 00:00:06.7760613
-- 4/22/2019 9:43:28 AM: AWV_for_2019.log: 9:43:14 AM Thread (4) ** END **
-- 4/22/2019 9:43:28 AM: AWV_for_2019.log: AWV_2019 - The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190422_094306.csv' because it is being used by another process.

-- 4/22/2019 9:43:28 AM: AWV_for_2019.log: Failed

Debbie_Peabody · April 24, 2019, 7:07pm

Try using our automation script with trial version of Toad Data Point 5.0.3. Do this on a copy of your script as it will upgrade to newer version.

sienm · April 25, 2019, 9:35pm

Hello Debbie,

Thank you for the reply. I have downloaded the beta and am still seeing this error:
-- 4/25/2019 2:26:36 PM: AWV_for_2019.log: AWV_2019 - The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190425_142617.csv' because it is being used by another process.

-- 4/25/2019 2:26:37 PM: AWV_for_2019.log: The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190425_142617.csv' because it is being used by another process.
-- 4/25/2019 2:26:37 PM: AWV_for_2019.log: System.Exception - AWV_2019 - The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190425_142617.csv' because it is being used by another process.

Inner exception:
System.ApplicationException - The process cannot access the file '\trillium\bp\Dataxfer_Tasks\Novillus\2019\AWV_20190425_142617.csv' because it is being used by another process.
at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)

-- 4/25/2019 2:26:37 PM: AWV_for_2019.log: Failed

Is there anything else I could try?

Debbie_Peabody · April 27, 2019, 12:28am

Let's have you open a support case. Then i can get the files needed to replicate the issue and send to TDP automation developer.

sienm · May 1, 2019, 2:29pm

Hello Debbie,

How do I open a support case?

Debbie_Peabody · May 1, 2019, 3:35pm

Use this link. https://support.quest.com/create-service-request

sienm · May 1, 2019, 4:21pm

I have been working with a tech internally and we figured out that we are getting this error because of the use of a volatile table in the code. Has this been a trending issue with automation? Are volatile tables not allowed?

Thank you for your prompt responses!

Debbie_Peabody · May 1, 2019, 7:20pm

It depends on the version of TDP, the type of volatile table and how you build and access them in the automation script. I can generally help you better through support as they know what files and data I need to investigate

sienm · May 1, 2019, 8:14pm

Sounds good, I will put in a service request!

sienm · May 1, 2019, 8:23pm

I am seeing this error when entering my license number:
license_error

Debbie_Peabody · May 1, 2019, 10:41pm

Sounds like you are not current on maintenance. Support does require current maintenance.

Debbie_Peabody · May 1, 2019, 10:52pm

You mentioned earlier that you thought the issue was due to using a temp table. One thing that might be different between running scheduled and running manually, is that manually you started the connection outside of automation. With automation, each activity with a connection is opened and closed for each connection.

There is an option to open a connection once and have automation only use that connection. It takes two items.

For the connection used, set the option to "Connect on startup".
In options turn on Environment | Connection Manager | Use connect on startup option running scheduled automation scripts

If the issue is that the temp table is gone or holding up the activity, these options will fix it

sienm · May 2, 2019, 2:33pm

I tried that and am still seeing the same error about the file being used by another process... hm...

sienm · May 2, 2019, 2:42pm

Another oddity is when I export to .xlsx it works, but when I export to .csv it fails. I need it to export to .csv

Debbie_Peabody · May 2, 2019, 7:25pm

You could try adding a pause activity after the export and before the email. If the issue is that the full export to file is not finished this might help.

I tried what you are doing and had no issues.

Why only export to csv? Is it because there are a lot of rows? If so, how many? Excel is very good about converting between csv and xlsx and visa versa.

rachelgomez161999 · February 13, 2023, 5:15am

Methods to fix-
Method 1. Reinstall the Xbox Application.
Method 2. Run the System File Checker (SFC Scan)
Method 3. Run the DISM Command.
Method 4. Run the CHKDSK Command.

Regards,
Rachel Gomez

Topic		Replies	Views
"The process cannot access the file ..." Automation Error Toad Data Point	2	2194	January 11, 2017
Error in Automation Script "Export_1 - The process cannot access the file 'K:\MMIS\RptRequests\GRakes\DistributorSpend.xlsm' because it is being used by another process." Toad Data Point	7	1300	May 12, 2017
The process cannot access the file Toad Data Point	4	659	October 14, 2022
Automation Script Error Toad Data Point	1	233	June 28, 2010
Toad Data Point 4.3.0.718 Windows 10 Issue Toad Data Point	3	1711	December 7, 2018

Automation Select to File Error

Related topics