Toad Automation Script Issue

Toad Data Point & Toad Intelligence Central
1

Hello everyone! I’m having an issue that when I call a SQL script from Toad Automation, it doesn’t seem to execute the statements within the script in consecutive order despite the log showing that it is.

Toad Data Point, v4.3.0.718 (32 bit)

Here is what my script does:
My script creates 6 tables (suppresses the already exists error if they already exist using a pragma command )
Truncates table 1 and then repopulates it based off a date from a table outside of the script
Truncates table 2 and then repopulates it based off of information from table 1
Truncates table 3 and then repopulates it based off of information from table 1
Truncates table 4 and then repopulates it based off of information from table 1
Truncates table 5 and then repopulates it based off of information from table 1
Truncates table 6 and then repopulates it using information from tables 2-5.
Drops tables 2-5.

When I run the script, by itself, it runs correctly with no issues and the results are what they should be.
I set the date in table 1 to be the 31st of July and set the date in the off-script table to be the 31st of August. I ran the script and table 1 picked up the August date and table 6 reflected those values

When I schedule the script using Toad Automation, I get unusual and inaccurate results. After the script finishes, I still have July 31st dates in table 1 and table 6 reflects the July values.

I separated out the table 1 steps into a 2nd script and had it execute before the main script in Toad Automation. This action corrected the dates, but the drop table statements at the end of the main script never executed.

I separated out the drop table portion of the script and had it execute after the main script in Toad Automation. That corrected the issue with the tables being dropped.

Then, I put all the pieces back into a single script and executed it from Toad Automation. I ended up with the July 31st dates again. I compared the Toad Automation Log to the messages generated when I run the script by itself and they are exactly the same.

Now that I’ve rambled on for way too long, the overview is that if I run the exact same script on its own and through Toad Automation, I get different results. This issue has occurred every time I’ve tried it.

Any help or explanation for this issue would be greatly appreciated.

2

You posted in the Toad for Oracle forum, which is a different product.

I moved your post to the Toad Data Point forum. They should be able to help you there.

3

Thanks, sorry for the confusion.

4

Do you have Automation script’s (.tas) calling other automation scripts (run Automation Script task)? Or do you have 6 execute script tasks one after the other in the same automation file? The latter should work. I have had trouble with execution order on the former method in a previous version of TDP.

5

Some of this might be confusing. So, I’ll give everything names. The actions that I detail above - creating, truncating, populating, etc. are all in the same TEST.SQL file.

When I use Toad to manually run TEST.SQL, it works just fine.

I also have a Toad Automation Script called SCHEDULE.TAS

When I use SCHEDULE.TAS to execute TEST.SQL, it completes without error, but the information in my final tables is inaccurate.

If I break TEST.SQL up into pieces - TEST1.SQL, TEST2.SQL, to execute each statement individually, in order, using SCHEDULE.TAS, it works just fine.

What I’m trying to figure out is what is happening when I just SCHEDULE.TAS to execute TEST.SQL that makes the information inaccurate, when it works individually or broken into pieces.

I’d rather my TEST.SQL remain as 1 file to reduce confusion for anyone else that has to run it or to make it easier to change if changes are needed. My best guess is that all the statements run concurrently when scheduled, but the log seems to indicate otherwise.

6

I am curious also as to why that is happening when you schedule it. Try adding a 5 second delay statement (WAITFOR DELAY ‘00:05’) in between your scripts 1 - 6 to see if that makes a difference. Just guessing at an idea here as I don’t understand why it looks like it is parallel processing in the first place.
There is under Tools/Options/Job Manger/Advanced, maximum number of threads: Try setting that to one and see if that cures it. Let us know if anything works.

7

What version of TDP are you using? We have had multiple requests on how to change the execute SQL activity. Mosts requests have been to inserting and preping a temp table. The statement is usually a select statement and we are preparing to use that as possible output.
I know this sounds silly but try adding a dummy select as the last statement or move the drop statement to a different execute. See if this changes the behavior