Export the Data to Excel via the Automation Designer

Toad for Oracle
1

Group,

I'm using Automation Designer to run a query and then export the data as an Excel file. I've used the "If..Then..Else" to start the run.

2727.Toad1.png

My first step is to connect to the server. This is working fine with no problems. In the next step, it's attempting to run the query via the "Export Dataset" which I've named "PDA Update". The properties look like this:

2046.Toad2.png
2046.Toad2.png662×574 21.1 KB

Toad3.png
Toad3.png1231×713 45.3 KB

Using this method, this always fails. I can run it manually with no issues, But if it is scheduled at a specific time, it fails. The error message says:

1/7/2016 6:15:11 AM: PDA Update (Export Dataset) started.
1/7/2016 6:15:16 AM: ERROR: The path %1 could not be found
1/7/2016 6:15:16 AM: PDA Update (Export Dataset) finished.

Can you help me determine why this is failing?

Toad4.png

For the record, I have other automated processes that are running with no issues. The only difference is these are using the "Execute Script" method. Any thoughts as to why it is failing?

In advance, thanks for your help.

Don

2

Does the export dataset action work on its own? Judging by the output message it may not like the output file path

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Friday, January 08, 2016 3:03 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

Export the Data to Excel via the Automation Designer

Thread created by donald.wilson

Group,

I’m using Automation Designer to run a query and then export the data as an Excel file. I’ve used the “If…Then…Else” to start the run.

My first step is to connect to the server. This is working fine with no problems. In the next step, it’s attempting to run the query via the “Export Dataset” which I’ve named “PDA Update”. The properties look like this:

Using this method, this always fails. I can run it manually with no issues, But if it is scheduled at a specific time, it fails. The error message says:

1/7/2016 6:15:11 AM: PDA Update (Export Dataset) started.

1/7/2016 6:15:16 AM: ERROR: The path %1 could not be found

1/7/2016 6:15:16 AM: PDA Update (Export Dataset) finished.

Can you help me determine why this is failing?

For the record, I have other automated processes that are running with no issues. The only difference is these are using the “Execute Script” method. Any thoughts as to why it is failing?

In advance, thanks for your help.

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

It works perfectly on it’s own! It does exactly what it is supposed to do with no issues. I have no idea why it doesn’t work via the Automation Designer.

4

Just so that we are clear, please do the following…

  1. Go to the automation designer and run the Export dataset action itself outside of the if/then/else (run selected action, rather than app) interactively. Does it work?

  2. If so, please run the if/then/else action with the TNS Ping and Export Dataset action interactively. Does that work?

I’m trying to determine if this error only occurs when it’s within an if…then…else or on its own.

Thanks

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Monday, January 11, 2016 7:30 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

RE: Export the Data to Excel via the Automation Designer

Reply by donald.wilson

It works perfectly on it’s own! It does exactly what it is supposed to do with no issues. I have no idea why it doesn’t work via the Automation Designer.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Brad,

I can tell you that the TNS ping (checking to see if the Database is accessible) is working fine whether or not it is accessed via the “If…Then…” or not. I can run the SQL script manually via Editor or from the Automation Designer by clicking that portion and running it. So it appears that the failure occurs only when its run automatically at it’s specific time.

Thanks again for the help!

Don

6

Brad, the more I’m thinking about this, the more I believe it has to do with the schema that I’m trying to run. By this I mean - I have three other automated scripts running that have no problems. These involve a schema attributed to my co-worker (called JONARKIR). The two I’m having problems with are run under my schema (DONACWIL). I’m assuming now that the logon is under one schema (JONARKIR) but trying to run the script for the other (DONACWIL). Assuming this is the case, what are the proper steps to set this up to run correctly?

Thanks,

Don

7

In that case, you might want to check your windows task and be sure the parameters are correct. In the App Designer, go to your scheduled items tab and find your app. R-click and view task properties. go to your actions tab and edit the “start a program” action. Do the parameters look good? They should look something like -a “App 1-> action1” or -a “App1”. Use these parameters and run this action manually from the command line. Does it work then? toad.exe -a “App1 -> action1”

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Monday, January 11, 2016 5:12 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

RE: Export the Data to Excel via the Automation Designer

Reply by donald.wilson

Brad,

I can tell you that the TNS ping (checking to see if the Database is accessible) is working fine whether or not it is accessed via the “If…Then…” or not. I can run the SQL script manually via Editor or from the Automation Designer by clicking that portion and running it. So it appears that the failure occurs only when its run automatically at it’s specific time.

Thanks again for the help!

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

8

View properties for the “Export Dataset” action in question and look in the session dropdown. Is this the right connection? No matter who is logged into Toad at any time, when this action runs it will use the connection shown in that session dropdown in the action properties.

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Tuesday, January 12, 2016 8:25 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

RE: Export the Data to Excel via the Automation Designer

Reply by donald.wilson

Brad, the more I’m thinking about this, the more I believe it has to do with the schema that I’m trying to run. By this I mean - I have three other automated scripts running that have no problems. These involve a schema attributed to my co-worker (called JONARKIR). The two I’m having problems with are run under my schema (DONACWIL). I’m assuming now that the logon is under one schema (JONARKIR) but trying to run the script for the other (DONACWIL). Assuming this is the case, what are the proper steps to set this up to run correctly?

Thanks,

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

9

Brad,

I’ve tested my theory and found it does set things up with the correct logon and schema. So we can eliminate that as an issue.

I presume to check the session drop down box, I click the icon that looks like an electrical plugin. Doing that displays the correct schema (DONACWIL@DWHPRD). There is a “(1)” after the name. I’m not sure if this makes any difference or not.

Is there something else to look for?

Thanks again,

Don

10

I still want to say this has something to do with your output path judging by the error given. Just for a test, what if you change your Export dataset output path to C:\ Does that make a difference?

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Tuesday, January 12, 2016 2:10 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

RE: Export the Data to Excel via the Automation Designer

Reply by donald.wilson

Brad,

I’ve tested my theory and found it does set things up with the correct logon and schema. So we can eliminate that as an issue.

I presume to check the session drop down box, I click the icon that looks like an electrical plugin. Doing that displays the correct schema (DONACWIL@DWHPRD). There is a “(1)” after the name. I’m not sure if this makes any difference or not.

Is there something else to look for?

Thanks again,

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

11

Brad, good news (I think). The script ran and move the output to my “C” drive. So apparently it has something to do with the network where I’ve been trying to save this to. I’m not sure why this is the case as it was working at one time. However now that I know it’s saving, I can use the new location of the output.

Thanks for the help. This is greatly appreciated!

Don

12

No problem! I’m glad we figured it out.

From: donald.wilson [mailto:bounce-donaldwilson@toadworld.com]

Sent: Wednesday, January 13, 2016 8:09 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export the Data to Excel via the Automation Designer

RE: Export the Data to Excel via the Automation Designer

Reply by donald.wilson

Brad, good news (I think). The script ran and move the output to my “C” drive. So apparently it has something to do with the network where I’ve been trying to save this to. I’m not sure why this is the case as it was working at one time. However now that I know it’s saving, I can use the new location of the output.

Thanks for the help. This is greatly appreciated!

Don

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

13

Muchas Gracias!!

14

re: ERROR: The path %1 could not be found error - could be because there are spaces in the output file path. Try creating a folder/file on the network drive without any space and run…it should go through ok.