Toad World® Forums

TDA 3.7 - TNS Connection Closed

Hi,

I have been using TDA for some time now and the one issue that always crops up is out of no where a job would fail because on the connection’s would get ‘corrupted’ on a step and the log file would display “TNS: Connection Closed”.

I understand what it means but is there any way to stop it happening?

I am running TDA 3.7 Base Edition on a windows server 2000 PC which has other versions of TDA on the same PC.

The way I fix this issue is by opening the job with the issue and re-selecting the database connection.

Rgds,

Kush

You can use a variable with a while loop and only set the variable value to get out of the loop when the query has been successful (or it hits the limit on # of tries that you can set). Uncheck the stop on error for the part that keeps failing. You can put a conditional step after the loop to test the variable and see if it exited the loop with success or failure (reached max tries) and then send a success or failure email. That way the job keep trying up to n number of tries before giving up. I had to use this on an unstable connection in the past. Usually my connection problem happened right away and once it connected it was good and only occasionally it got interrupted.

Hi Greg,

Thanks for your response however it’s not that we are losing connection to our database as it is very stable, its that in TDA sometimes the connections that I have setup get corrupt.

To fix i have go into the job and reconnect to the database - at this point i know the database is up and running because i am running queries on toad for oracle fine.

Any other suggestions - a caching issue in the tda setup folder?

Rgds,

Kus

How many TNS homes do you have? If you have installed the Orcacle client more than once it can cause intermittent confusion. Unistalling the Oracle clients did not help. Our IT guru had to go online to find out how to fix it. It involved unistalling, removing registy entries and files that the unistall left behind and then installing one Oracle client. Got to get in there and rip it out by the roots. After that jobs kept working.

Hi Greg,

I realised what the issue was.

I have a TDA job which has a batch file at step 1, which checks if a certain file is in a directory. It will loop every 10 minutes to check if the file is there if not it will ‘sleep’ until it has fouind it.

Our database shutsdown at 10pm at which point TDA had closed the connection.

I’ll look into using your suggestion as it seem’s like the most viable solution.

Would you happen to have an example tda project so i can see how you set it up?

Rgds,

Kush

I would like to remind that starting current beta that was posted a week ago, Run activity does not generate errors anymore if exit code of a program/bat file that is run by that Run activity is equal or greater 16384 (0x400). If you use codes from this range, you will not need to set Continue on Error.

Igor.

Sorry, I did this at a former place of work but I created a basic example for you. See attached TestConnect.tas file Not letting me upload the file. Email me Gregory.Davis@abqhp.com ( that is gregory.davis at abqhp.com if it removes the email address) and I will send you the .tas file.