Need a method to disconnect database connections once automation is complete


#1

In automation I am running queries against an excel file, Teradata, local storage and hive, I need to close these connections after the automation is complete. TOAD makes the excel file read only, so maintaining the connection prevents any changes to the source data or a new file to be saved. I have a limit on concurrent connections to Teradata and hive and would just prefer local storage be disconnected.

Thanks


#2

Hello tm,

are you running this automation script on your local PC?.

If so, i would suggest you to install Toad Intelligence Central on server and upload the script there. The scheduler embedded in TIC should take care of closing connection.

Best regards Jakub.


#3

By default when automation scripts are scheduled and run they open an close the connection after each activity. This is the default behavior unless you have set the attribute to use Auto connected connections. This means you have the option check to start the connection on start up. So i am not sure what you saying.

Are you saying that you have an automation script, schedule it to run and after toad shuts down the connections are still connected? i don’t see how that can be.


#4

Hi Debbie,

In this case I am manually running an automation script from within TOAD Data Point, when running a scheduled task it does disconnect, however I have to wait for an email and save the file to a server then kick off the automation. When kicked off from TOAD the connections remain open after the automation completes.

Tom


#5

Jakub,

I’ll take a look at that.

Thanks


#6

Usually user open Toad and connect and run automation scripts with the connection open. Have you tried opening Toad, NOT connecting and then running the script? I believe that it will open and close the connections.


#7

I’ll try executing from project manager and see if it works. Is there any value in having a disconnect option for those instances where automation is run with in TOAD? I will run a script several times during development and those connections would remain open. If a new excel files comes in during development or editing it will not be able to be updated as TOAD will have it locked open.

Thanks,

Tom


#8

Did you ever figure this out? I have a similar issue. I have an excel linked to an access DB. The business updates the excel daily and my automation is supposed to loop every 30 minutes and look for this updated file. They cannot overwrite the excel because the DB connection is constant and does not disconnect from access during the pause in the automation. Toad Data Point version 4.0.0.624


#9

rossjustin,

I have tried all suggested options except Intelligence Central and none have worked other than manually disconnecting the Excel connection.

Tom


#10

We are working on a solution for this now. (QAT-11935) It is being a bit persnickety but we might have a fix in next Beta (two weeks from now).

We normally close the connection associated to the activity but in the case of XQuery we do not. Even that is not normally a problem unless Excel is involved and that file is made within the script and then used.

Even though we are “connecting” to excel like it is a database is it not really and does not support concurrency, etc. So what you would expect in normal database is not available. As an immediate work around I would export the data to Local Storage (If you have pro edition) or Toad Sample Database (bundled access database with Base edition). Both Local Storage and Access are real databases and should give you what you want for use in XQuery.

Otherwise wait a couple of weeks and see if we got this fixes. (I am hopeful)


#11

Good news! Thanks very much


#12

This is now available in the latest Beta