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