Simple Automation script with INSERT SQL fails to execute because the export file is being used by another process

[scac1041]

Hi,

I recently started using Toad for automating some of the tasks I perform daily. Mine is a Toad for Data Analysts, v 2.6.1.568, on a Windows 7, 32 bit machine.

When I manually run the script, it works perfectly. However, when I try to automate the script, it always ends up with the error described in the Subject.

Since I am a beginner at this, I don’t understand at all what is going wrong, and what the error message means - how come a file which will be generated anew once some data is exported to it be in use already?

I appreciate any help.
The log file is as follows:

– 3/20/2015 12:49:01 AM: Script_1.log: Build started
– 3/20/2015 12:49:12 AM: Script_1.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.
– 3/20/2015 12:49:12 AM: Script_1.log: Build Completed
– 3/20/2015 12:49:13 AM: Script_1.log: Connecting to DWRAC (AIMIA), AIMIA
– 3/20/2015 12:49:14 AM: Script_1.log: Connection successful.
– 3/20/2015 12:49:14 AM: Script_1.log: Begin Export
– 3/20/2015 12:49:15 AM: Script_1.log: SQL to execute:
–TRUNCATE TABLE lp_redeem_data_temp;

–
–INSERT INTO lp_redeem_data_temp (time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category,
points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT t1.*,
– t2.rdm_type,
– t3.face_value,
– t4.package_description,
– t4.corp_pkg_flag,
– t4.pay_type_code
–FROM
– (
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– trans_ID,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– (num_points) points_denomination
– FROM lp_redeem_trans@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND item_refunded <> ‘Y’
– UNION ALL
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– trans_ID,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– (num_points) points_denomination
– FROM lp_redeem_trans_archive@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND item_refunded <> ‘Y’
– ) t1
–LEFT JOIN rdmtyp_lookup t2
– ON t1.item_category = t2.item_category
– AND t1.item_sub_category = t2.item_sub_category
–LEFT JOIN nonmpay_prtnr_rdm_tbl t3
– ON t1.item_category = t3.partner
– AND t1.item_sub_category = t3.partner_name
– AND t1.points_denomination = t3.points
–LEFT JOIN mgate_package_info@emaprod t4
– ON t1.package_id = t4.package_id;

–COMMIT;

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘lp_redeem_data_temp’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM lp_redeem_data_temp;

–COMMIT;

–

–TRUNCATE TABLE mpay_redeem_data_temp;

–
–INSERT INTO mpay_redeem_data_temp (time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category,
points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT t1.time_stamp,
– t1.redemption_date,
– t1.trans_ID,
– t1.account_id,
– t1.msisdn,
– t1.package_id,
– t1.redeem_type,
– (t2.distributor_name) item_category,
– (t2.distributor_name) item_sub_category,
– points_denomination,
– (t1.redeem_type) rdm_type,
– t1.face_value,
– t3.package_description,
– t3.corp_pkg_flag,
– t3.pay_type_code
–FROM
– (
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– TO_CHAR(trans_ID) trans_ID,
– account_id,
– msisdn,
– package_id,
– ‘Partner’ redeem_type,
– (item_points) points_denomination,
– (item_value) face_value,
– dist_id
– FROM mpay_redeem_trans@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND refunded = ‘0’
– AND trans_type = ‘1’
– UNION ALL
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– TO_CHAR(trans_ID) trans_ID,
– account_id,
– msisdn,
– package_id,
– ‘Partner’ redeem_type,
– (item_points) points_denomination,
– (item_value) face_value,
– dist_id
– FROM mpay_redeem_trans_archive@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND refunded = ‘0’
– AND trans_type = ‘1’
– ) t1
–LEFT JOIN mpay_distributor@lpapp t2
– ON t1.dist_id = t2.distributor_id
–LEFT JOIN mgate_package_info@emaprod t3
– ON t1.package_id = t3.package_id;

–COMMIT;

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘mpay_redeem_data_temp’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM mpay_redeem_data_temp;

–COMMIT;

–

–TRUNCATE TABLE weekly_redemption_data_temp;

–

–INSERT INTO weekly_redemption_data_temp (partition_date, time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category,
item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT time_stamp partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM lp_redeem_data_temp
–WHERE rdm_type = ‘Mobily’
– OR (item_sub_category <> ‘EVOUCHER’ AND rdm_type = ‘Partner’)
–UNION ALL
–SELECT time_stamp partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM mpay_redeem_data_temp;

–COMMIT;

–

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘weekly_redemption_data’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM weekly_redemption_data_temp;

–COMMIT;

–

–
–INSERT INTO weekly_redemption_data (partition_date, time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category,
item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM weekly_redemption_data_temp;

–
–COMMIT;

–

–SELECT redemption_date,
– pay_type_code,
– rdm_type,
– points_denomination,
– item_sub_category,
– SUM(points_denomination) total_points,
– COUNT(msisdn) transaction_count,
– SUM(face_value) total_face_value
–FROM weekly_redemption_data_temp
–WHERE corp_pkg_flag = 0
–GROUP BY redemption_date,
– pay_type_code,
– rdm_type,
– points_denomination,
– item_sub_category
–ORDER BY redemption_date,
– pay_type_code,
– rdm_type;

–

–

– 3/20/2015 12:49:15 AM: Script_1.log: Beginning export.
– 3/20/2015 12:50:06 AM: Script_1.log: The process cannot access the file ‘C:\Users\u.gupta.aim\Desktop\Ali\Automation\data_extract\weeklyRedempData2015-03-20 00-
49-13.csv’ because it is being used by another process.
– 3/20/2015 12:50:06 AM: Script_1.log: at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)
– 3/20/2015 12:50:06 AM: Script_1.log: Failed

[GregDavis11009]

Been a long time since I have used 2.6. I remember 2.7 being much better in automation but I can’t remember what 2.6 had problems with specifically. I would recomend upgrading, a lot has changed since then. You could not reference a file that did not exist even if it would be built in a prior step used to be a problem in older versions. Make sure the file is not open by another application, other than that upgrade.

[Debbie_Peabody]

I would highly suggest you upgrade to latest version with is 3.6.1.

The error suggests you are using the Select to File activity but your SQL script suggests you need to use the Execute SQL activity. I can help better if you post the *.tas file, dependant files and the last log file.

[scac1041]

Thank you for your responses, GregDavis11009 and Debbie.

Debbie,

You are correct that with my SQL script, I am

1. truncating some temporary tables I created earlier,

2. extracting and inserting data into the temporary tables from some base tables,

3. calculating some metrics from the temporary tables and inserting the metrics into a penultimate table

4. inserting the data from the penultimate table into a main table

5. selecting the data from the penultimate table into an Excel file (for which I use the Select to File activity)

The *.tas file is as follows:

<?xml version="1.0" encoding="utf-16"?><![CDATA[

<ta0:DefaultDescriptionActivity Enabled=“True” Subject=“Automation Script Error” Authentication=“Version=1,AuthenticationMethod=None” SmtpPort=“25” LoggingLevel=“Verbose” ScriptName=“weekly_redempData_script” x:Name=“weekly_redempData_script” LogDir=“C:\Users\u.gupta.aim\Desktop\Ali\Automation\Logging” Body="" To="aliarsalan.kazmi@aimia.com" EmailOnError=“False”>
ta0:DefaultDescriptionActivity.AttachmentFiles
<x:Array Type="{x:Type ta0:FileDescription}">
<ta0:FileDescription FileName=“C:\Users\u.gupta.aim\Desktop\Ali\Automation\Logging\weekly_redempData_script.log” />
</x:Array>
</ta0:DefaultDescriptionActivity.AttachmentFiles>
</ta0:DefaultDescriptionActivity>



<ta1:DatabaseConnectionActivity x:Name=“Connection_1” Description=“Database Connection”>
ta1:DatabaseConnectionActivity.Connection
<ta1:ConnectionDescription Trl=“oracle://AIMIA@DWRAC/” Description=“DWRAC (AIMIA), AIMIA” IsNullPassword=“False” NodePath=“Oracle\DWRAC (AIMIA), AIMIA” />
</ta1:DatabaseConnectionActivity.Connection>
<ta1:SelectToExcelActivity x:Name=“weekly_redempData” RowCountVar="" SqlScript=“C:\Users\u.gupta.aim\Desktop\Ali\Automation\weekly_redempData_v2.sql” Description=“Execute SQL and save results to file” Suffix="#yyyy-MM-dd HH-mm-ss#" ExcelFileName=“C:\Users\u.gupta.aim\Desktop\Ali\Automation\data_extract\weeklyRedempData.csv”>
ta1:SelectToExcelActivity.ExportInstanceOptions
<ns1:ExcelExportInstanceOptions FileOverwrite=“True” FileDirctory=“C:\Users\u.gupta.aim\Desktop\Ali\Automation\data_extract” FileSuffix="#yyyy-MM-dd HH-mm-ss#" AppendTimeStampToNamedWorksheet=“False” FileName=“weeklyRedempData” ExcelFile=“False” ClearWorksheet=“False” xmlns:ns1=“clr-namespace:Quest.Toad.ImportExport;Assembly=ToadCore, Version=4.6.1.568, Culture=neutral, PublicKeyToken=c862883b2ae69822”>
ns1:ExcelExportInstanceOptions.FormatSettings
<ns1:FormatSettings FormatType=“CommaSeperatedValues” Row=“1” HeaderRow=“True” QuoteChar="’" Delimiter="," FileFilter=“Excel (2007) Files (.xlsx)|.xlsx | Excel (.xls)|.xls” BlockInsertValue=“500.0” Column=“1” FileExtension=“csv” ExcelAtLocation="{x:Null}" ColumnWidth=“50” HeaderBackColor=“LightGray” />
</ns1:ExcelExportInstanceOptions.FormatSettings>
</ns1:ExcelExportInstanceOptions>
</ta1:SelectToExcelActivity.ExportInstanceOptions>
</ta1:SelectToExcelActivity>
</ta1:DatabaseConnectionActivity>
</ta:ToadAutomationScript>]]>

The last log file is the following:

– 3/21/2015 9:01:19 PM: Script_1.log: Build started
– 3/21/2015 9:01:34 PM: Script_1.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.
– 3/21/2015 9:01:34 PM: Script_1.log: Build Completed
– 3/21/2015 9:01:34 PM: Script_1.log: Connecting to DWRAC (AIMIA), AIMIA
– 3/21/2015 9:01:34 PM: Script_1.log: Connection successful.
– 3/21/2015 9:01:34 PM: Script_1.log: Begin Export
– 3/21/2015 9:01:35 PM: Script_1.log: SQL to execute:
–TRUNCATE TABLE lp_redeem_data_temp;

–
–INSERT INTO lp_redeem_data_temp (time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT t1.*,
– t2.rdm_type,
– t3.face_value,
– t4.package_description,
– t4.corp_pkg_flag,
– t4.pay_type_code
–FROM
– (
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– trans_ID,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– (num_points) points_denomination
– FROM lp_redeem_trans@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND item_refunded <> ‘Y’
– UNION ALL
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– trans_ID,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– (num_points) points_denomination
– FROM lp_redeem_trans_archive@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND item_refunded <> ‘Y’
– ) t1
–LEFT JOIN rdmtyp_lookup t2
– ON t1.item_category = t2.item_category
– AND t1.item_sub_category = t2.item_sub_category
–LEFT JOIN nonmpay_prtnr_rdm_tbl t3
– ON t1.item_category = t3.partner
– AND t1.item_sub_category = t3.partner_name
– AND t1.points_denomination = t3.points
–LEFT JOIN mgate_package_info@emaprod t4
– ON t1.package_id = t4.package_id;

–COMMIT;

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘lp_redeem_data_temp’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM lp_redeem_data_temp;

–COMMIT;

–

–TRUNCATE TABLE mpay_redeem_data_temp;

–
–INSERT INTO mpay_redeem_data_temp (time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT t1.time_stamp,
– t1.redemption_date,
– t1.trans_ID,
– t1.account_id,
– t1.msisdn,
– t1.package_id,
– t1.redeem_type,
– (t2.distributor_name) item_category,
– (t2.distributor_name) item_sub_category,
– points_denomination,
– (t1.redeem_type) rdm_type,
– t1.face_value,
– t3.package_description,
– t3.corp_pkg_flag,
– t3.pay_type_code
–FROM
– (
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– TO_CHAR(trans_ID) trans_ID,
– account_id,
– msisdn,
– package_id,
– ‘Partner’ redeem_type,
– (item_points) points_denomination,
– (item_value) face_value,
– dist_id
– FROM mpay_redeem_trans@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND refunded = ‘0’
– AND trans_type = ‘1’
– UNION ALL
– SELECT time_stamp,
– TO_CHAR(time_stamp, ‘YYYYMMDD’) redemption_date,
– TO_CHAR(trans_ID) trans_ID,
– account_id,
– msisdn,
– package_id,
– ‘Partner’ redeem_type,
– (item_points) points_denomination,
– (item_value) face_value,
– dist_id
– FROM mpay_redeem_trans_archive@lpapp
– WHERE time_stamp >= TO_DATE(SYSDATE - 7) and time_stamp < TO_DATE(SYSDATE)
– AND status = 2
– AND refunded = ‘0’
– AND trans_type = ‘1’
– ) t1
–LEFT JOIN mpay_distributor@lpapp t2
– ON t1.dist_id = t2.distributor_id
–LEFT JOIN mgate_package_info@emaprod t3
– ON t1.package_id = t3.package_id;

–COMMIT;

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘mpay_redeem_data_temp’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM mpay_redeem_data_temp;

–COMMIT;

–

–TRUNCATE TABLE weekly_redemption_data_temp;

–

–INSERT INTO weekly_redemption_data_temp (partition_date, time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT time_stamp partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM lp_redeem_data_temp
–WHERE rdm_type = ‘Mobily’
– OR (item_sub_category <> ‘EVOUCHER’ AND rdm_type = ‘Partner’)
–UNION ALL
–SELECT time_stamp partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM mpay_redeem_data_temp;

–COMMIT;

–

–
–INSERT INTO weeklyTables_log (generation_date, table_name, total_count, unique_msisdn)
–SELECT TO_DATE(SYSDATE) generation_date,
– ‘weekly_redemption_data’ table_name,
– COUNT(*) total_count,
– COUNT(DISTINCT msisdn) unique_msisdn
–FROM weekly_redemption_data_temp;

–COMMIT;

–

–
–INSERT INTO weekly_redemption_data (partition_date, time_stamp, redemption_date, trans_ID, account_id, msisdn, package_id, redeem_type, item_category, item_sub_category, points_denomination, rdm_type, face_value, package_description, corp_pkg_flag, pay_type_code)
–SELECT partition_date,
– time_stamp,
– redemption_date,
– trans_id,
– account_id,
– msisdn,
– package_id,
– redeem_type,
– item_category,
– item_sub_category,
– points_denomination,
– rdm_type,
– face_value,
– package_description,
– corp_pkg_flag,
– pay_type_code
–FROM weekly_redemption_data_temp;

–
–COMMIT;

–

–SELECT redemption_date,
– pay_type_code,
– rdm_type,
– points_denomination,
– item_sub_category,
– SUM(points_denomination) total_points,
– COUNT(msisdn) transaction_count,
– SUM(face_value) total_face_value
–FROM weekly_redemption_data_temp
–WHERE corp_pkg_flag = 0
–GROUP BY redemption_date,
– pay_type_code,
– rdm_type,
– points_denomination,
– item_sub_category
–ORDER BY redemption_date,
– pay_type_code,
– rdm_type;

–

–

– 3/21/2015 9:01:35 PM: Script_1.log: Beginning export.
– 3/21/2015 9:01:46 PM: Script_1.log: The process cannot access the file ‘C:\Users\u.gupta.aim\Desktop\Ali\Automation\data_extract\weeklyRedempData2015-03-21 21-01-34.csv’ because it is being used by another process.
– 3/21/2015 9:01:46 PM: Script_1.log: at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)
– 3/21/2015 9:01:46 PM: Script_1.log: Failed

Could you please elaborate about the dependent files? If I understand correctly, I am not using any of those - all I am attempting to do is extract data from the database, populate certain tables, and then select data from one of those tables to an Excel file.

Thanks for the help!

[GregDavis11009]

I think you hit a bug. There is no way that the filename with a timestamp in the name down to the minute can be open by anything other than Toad itself.

[Debbie_Peabody]

The select to file activity is set up parse a script and export each parse into a separate worksheet. For example if you had the following script you would get three worksheets in the same excel file with the the result of each SQL in a worksheet.

select * from a:

select * from b:

select * from c:

In your case you have several inserts and then a select. I see that your connection type is Oracle. In some databases like SQL Server they can execute several statements as a batch and still have result sets. But not so with Oracle.

I suggest you do all of your table set up in an Execute Script. Then put the last select in a Select to File. You can follow this up with a drop of your working table in an execute script.