WinSQL » Knowledge base
|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
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:
- 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.
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.