The SQL that I am running creates a TEMP table and then does a select query off of that. The SQL runs fine in TOAD 3.7 by it’s self but when I run as automated task to export to csv it errors out.
The error is that the temp table doesn’t exist. Again this does not happen when I just run the SQL.
Any ideas? I’m having a lot of issues with automation.
Thank You!
We made a change in Automation where each activity opens and closes the connection. You need the connection to stay open so the temp table will persist.
We have provided for this. You need to make two changes.
In the connection manager select the option that says to start your connection on start up.
In Options under Environment | Connection Manager select the option to “Use connect on start up option running scheduled automation scripts”
This will open up a connection on start of TDP. When the automation script see there is already a connection of the type it needs it will use it and not close the connection allowing you to keep your temp table. [Y]
Thank you debbie! When I got to Tools Options under Environment | Connection Manager I don't see any options to select just a dropdown window for "Connection Import/Export Folder"
what version do you have? I believe we added this to TDP 3.8. You should down load and upgrade to latest version which is TDP 3.8.1
3.7
I work for a large institution so not sure if I can do that but will try. Is there any work arounds for 3.7?
I got upgraded to 3.8.05
I think you are referring to TDP 3.8.0.575. That has the option that you need. The latest is a patch TDP 3.8.1…677 that had a fix for those that use 64-bit Oracle Client and .net 4.6.1.
To answer your last question --> there are no work arounds for TDP 3.7
Yes 8.0.575 is the exact version. I’m still getting this issue where everything runs perfect if I run it just as an SQL query manually and copay and paste data, but fails if I use it to automate the export. This SQL has been run with toad perfectly manually and automated with netezza for over 6 months so I don’t think it is the SQL.
I have mulitple other queries that are working perfect with automation. It seems to be something to do with temp tables. Below is the error.
Adding Export Notification Subscriber {0}
– 7/15/2016 9:39:23 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Export Started [7/15/2016 9:39:22 AM]
– 7/15/2016 9:39:23 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) ** START **
– 7/15/2016 9:39:24 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Export using connection: ATLAS PROD (zklwch5)
– 7/15/2016 9:39:24 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Export Wizard: Building object list
– 7/15/2016 9:39:24 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Build List done.
– 7/15/2016 9:39:25 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Exporting Data (1 of 1)
– 7/15/2016 9:39:25 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) BEGIN Query :
– 7/15/2016 9:39:26 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) Object SQL Query started.
– 7/15/2016 9:39:26 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (10) InternalReadBackground - start
– 7/15/2016 9:39:26 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (10) InternalReadBackground - exception ERROR: 'CREATE TEMP TABLE M…
– 7/15/2016 9:39:27 AM: 5GRulesetCheckToad.log: error ^ found “” (at char 1124) expecting an identifier found a keyword
– 7/15/2016 9:39:27 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (10) InternalReadBackground - stack at Quest.Toad.NativeOdbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
– 7/15/2016 9:39:27 AM: 5GRulesetCheckToad.log: at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
– 7/15/2016 9:39:28 AM: 5GRulesetCheckToad.log: at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
– 7/15/2016 9:39:28 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (9) DoneReadTreadWriter
– 7/15/2016 9:39:28 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (9) DoneReadThreadWriter finally - adapter.RowsRead 0
– 7/15/2016 9:39:29 AM: 5GRulesetCheckToad.log: 9:39:22 AM Thread (4) END Query :
Hello, I'm using Toad Data Point 5.1.0.142 and i followed the instructions to have it be able to use temp tables in automation (connect on startup and select "use connect on startup option"), but when i run a script manually, it's losing the temp table. Is there any way to have it keep the sql server temp table for an entire execution of the automation script?
