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

**URL:** <https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [March 20, 2015, 7:18am UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948 "2015-03-20T07:18:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![scac1041](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@scac1041](https://forums.toadworld.com/u/scac1041)\
**Post date:** [March 20, 2015, 7:18am UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/1 "2015-03-20T07:18:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\
**Post date:** [March 20, 2015, 7:37am UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/2 "2015-03-20T07:37:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 20, 2015, 5:44pm UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/3 "2015-03-20T17:44:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![scac1041](https://avatars.discourse-cdn.com/v4/letter/s/ac91a4/32.png) [@scac1041](https://forums.toadworld.com/u/scac1041)\
**Post date:** [March 21, 2015, 11:20am UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/4 "2015-03-21T11:20:13Z")

</div>

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](mailto:To=%22aliarsalan.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!

---

<div class="post-metadata">

**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\
**Post date:** [March 23, 2015, 8:01am UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/5 "2015-03-23T08:01:52Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 23, 2015, 7:50pm UTC](https://forums.toadworld.com/t/simple-automation-script-with-insert-sql-fails-to-execute-because-the-export-file-is-being-used-by-another-process/32948/6 "2015-03-23T19:50:01Z")

</div>

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.
