Automate

 View Only

  • 1.  Scheduling a Query script to update a specific Excel file

    Posted 01-14-2019 16:02
    Hello, 

    I'm using Winshuttle Foundation 11.2.12. 

    I' trying to fin a way to update an Excel file automatically by scheduling a query script.

    When scheduling the query on the server, it doesn't update the Query Template, it generate a new file in the Query Data files library. I don't want to create a new file each time we run the Query, I want the unique file to be updated each time with the most recent data. 

    When scheduling the query locally, I can actually update the same file each time, but my laptop needs to be turned on in order to run the query. That is not good since I want to run the query at 1am and also if I'm of for vacation, the file will not get updated. 

    Is there a way to update a unique file during the night without turning on my laptop? When generating a new file on the server, is there a way with composer or another tool to overwrite an existing file with the file that was just created? 

    Thank you for your suggestion.

    ------------------------------
    Marc Desjardins | IT Project Lead
    Pharmascience Inc | Montreal | +1.438.315.3357
    ------------------------------


  • 2.  RE: Scheduling a Query script to update a specific Excel file

    Posted 01-15-2019 10:13
    ​The only way I've been able to schedule query scripts to run 'on server' is by using an SQL Server database as the output destination (I think outputting to a sharepoint list is also possible).  I have numerous queries that run daily/weekly for updating various tables for process automation activities, and I can easily create additional routines to extract data from those tables to specific files/locations.

    The documentation for scheduling Query scripts isn't very clear (imo) with regards to what you can output to from 'scheduled on server' Query script jobs.  I was once trying to schedule jobs to output to an Access database on a network location, but found out through trial and error that this wasn't possible.

    Part of the issue with scheduling on server is that the server has to have access to the file that you are trying to output your Query script data extract to, and obviously it's not going to be able to update a file that is local to your laptop, or even a file that exists in a shared network location that the server may not have access to.  When I set up a job to output data to an SQL Server database, I have to set up a connection to that database and provide my login credentials, so that the query can extract data even while I'm offline; only downside is that when my credentials expire every three months, I have to delete the jobs and re-create them from scratch.

    ------------------------------
    Jeremie Dippel
    Project Engineer @ Rockwell Automation
    Wisconsin Local Wug Leader
    I'm your huckleberry.
    ------------------------------

    Original Message