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, v18.104.22.1688 (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.