How to schedule a SQL query in 3 steps

Introduction

There are a lot of cases where you want to run a certain query on a fixed schedule. For business it is most useful to have the results in Excel and sent by email. Although most databases have the possibility to schedule, this is only possible with specific authorizations which are difficult to get from your local DB administrator. Next to that, having true Excel output is often a challenge.

With AutoSQL you can easily set up an automated job based on a query, output format and mail options. Once you have set this up, you can schedule it directly with Windows Task Scheduler.

Step 1: Setting up an ODBC connection

The ODBC standard exists for a long time and is supported by almost all databases. AutoSQL uses ODBC connections to execute queries. From the AutoSQL GUI you can select existing ODBC Data sources. So the first step is to setup an ODBC data source if not already available.

Installing needed drivers

Make sure you have an ODBC driver installed for your database. Windows typically has a driver available for all Microsoft products (MS SQL, Access) but outside of those you need to install them yourself. Drivers for common database can be found here:

Create data source

One you have the driver installed, create an ODBC data source by going to ‘Control Panel’ -> ‘Administrative tools’ -> ‘ODBC Data Sources’. If you have a 64 bit OS, you probably see a 32 bit and 64 bit version. It depends on the architecture of the ODBC driver installed which one you need.

1. Click on ‘Add…’ to create a new data source:


2.Choose the driver you need, in this example we choose the MySQL driver:


3. Depending on the database, you’ll get a different screen. However, all of them will required a server address, a username and password:


Once you click ‘Ok’, the data source is created and can be used in AutoSQL.

Step 2: Setting up the AutoSQL job

Depending on the architecture of your ODBC data source (32 bit or 64 bit), start the corresponding version of AutoSQL.

Setup query

1. Click on ‘Get DSN’ to see a list of available ODBC data source.


2. Here we choose ‘MySQL’ and put in our username and password (this is not saved as part of the data source itself).


3. Now let’s put in the query that we want to execute to list all employees that have their birthday in the coming 14 days:


4. If you click on ‘Test Query’ you can test if both the connection and the query work. It will list the first 50 records (max).


Define output

The ‘Query’ tab is now complete and we will continue in the ‘Output’ tab. Here we specify we want to save the result in an Excel file and send it by mail to the HR department.


Some options we have enabled:

  • Overwrite the same file every time it’s updated. You can also append or put a date placeholder in the filename to create unique files.
  • We only want to receive the results by mail if there are any. This is done with the ‘Only when more than 0 rows’ option enabled. If the query returns 0 rows, it will not send an email.
  • We want the output file to be attached to the email if it does not exceed 5 MB. If it is larger than 3 MB, it will first be zipped.
  • Possible place holders in the email text: output path, number of rows, date, query definition.


Before we can test the job, we need to specify a mail server to use. Click in the left navigation pane on ‘General settings’.


In the generic settings we specify:

  • A SMTP server to use
  • A log file directory
  • A mail address to send the log file to in case something went wrong


Now go back to the action and click ‘Test’ to see if everything works as expected.


Step 3: Scheduling the job

If your test was successful, the last step is to schedule it.


1. Click ‘Get Background Command’. This will copy a background execution command to your clipboard. In this case it will be C:\Program Files\AutoSQL\autosql_batch32.exe C:\AutoSQLFile\MySQLExample.xml. The XML file contains the job details that we just created.


2. Open the Task Scheduler and choose ‘Create task…’


3. Go to the ‘Actions’ tab and click on ‘New’


4. Now paste the clipboard content in the ‘Program/script’ field and click ‘Ok’.


5. It will ask if you want to split your program and argument, click ‘Yes’:


6. No go to the ‘Triggers’ tab and click ‘New’. Here you can define any schedule you like. We scheduled it to run every Monday morning.


Now give it a name and save it. That’s all!