WinSQL » Knowledge base

Document information

Document ID:1455
Subject:Running scheduled queries to a different output file
Creation date:8/18/10 10:28 AM
Last modified on:12/6/10 3:00 PM


Export queries to custom text files


Consider the following problem
  • You need to save the results of a SELECT query to a text file
  • You need to run query every night using the scheduler
  • You need to change the file name so that it creates a new file every day, or the file names contain the name of the weekday, for example Sunday_Data.txt, Monday_Data.txt and so on.

IMPORTANT: These steps will only work if you are using version 8.5 or above for WinSQL Professional

Steps

The first step is to create an export template and then running this export template using the scheduler. Following steps demonstrate how to accomplish these tasks.
  • First, connect to the desired database, type the SELECT statement in the query window, make sure it works and then select Export Results from the menu. See screen shot below

  • This will invoke the export query wizard. Select a new template and then pick Export to an external text file. See image below



  • Next specify a file name for export. Notice the use of a variable in the file name, which is currently set to $CURRENT_DAY$. The value of this variable will be replaced by the actual name of the day at run time.



    In current example, the file name is set to $CURRENT_DAY$_Customer.txt. This will cause WinSQL to create files like:
    • Sunday_Customer.txt
    • Monday_Customer.txt
    • ...
    • and so on


    In addition to $CURRENT_DAY$, you can use the following variables

    • $CURRENT_DATETIME$ - Creates a file name with the current time stamp. The format will be MM_DD_YY_Hour_Minute_Second
    • $CURRENT_DATE$ - Creates a file name with current date. The format will be MM_DD_YY
    • $CURRENT_TIME$ - Creates a file name with current time. The format will be Hour_Minute_Second


  • Click the Next button and specify a template file. You will have to specify this template file in the Scheduler.



  • Once the export template is saved, invoke WinSQL's Scheduler wizard.



  • Create a new task, select Run Data Export Wizard from available task types. Select the export template you created in the previous steps.



    Once a task is created you can create a schedule to run it.


User comments

Posted by Jared on 11/30/10 6:47 PM

This didn't work for me at all. All I get for a file name is $CURRENT_DATE$Filename.txt. Am I doing something wrong when inputting the $CURRENT_DATE$ variable?


Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com