Need a method to disconnect database connections once automation is complete

Follow / 25 Jan 2018 at 5:26am

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

Replies

  • Follow / 25 Jan 2018 at 8:51am

    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.

  • Follow / 25 Jan 2018 at 2:02pm

    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. 

  • Follow / 25 Jan 2018 at 10:04pm

    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

  • Follow / 25 Jan 2018 at 10:06pm

    Jakub,

    I'll take a look at that.

    Thanks

  • Follow / 27 Jan 2018 at 3:44am

    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. 

  • Follow / 29 Jan 2018 at 5:28pm

    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

  • Follow / 13 Apr 2018 at 2:25pm

    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

  • Follow / 13 Apr 2018 at 3:47pm

    rossjustin,

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

    Tom

  • Follow / 13 Apr 2018 at 8:20pm

    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)

  • Follow / 16 Apr 2018 at 6:06am

    Good news!  Thanks very much