schedule a sql query in 3 steps

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.

Step 1: Setup an ODBC connection

Search for ‘ODBC’ in your Windows menu, or go to the Control Panel -> Administrative Tools -> ODBC Data source’. 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.

To create a new one, click on ‘Add…’. You will be presented with a list of database types (drivers).

For each database, a specific driver needs to be installed. If your database is not in the list, please download it and install it first. The ODBC drivers for the most common databases can be found here:

MySQL
https://dev.mysql.com/downloads/connector/odbc/

PostgreSQL:
https://odbc.postgresql.org/

Oracle:
http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-098155.html

In our example we will work with Microsoft SQL server. This is driver is typically already installed on any Windows installation. 

1. Select the SQL server you want to connect to and give it a name. Rember it, as you need it later in AutoSQL.

2. Select the way you want to authorize on the SQL server. Either to integrated Windows, or a specific database user

3. Choose the default database, and click ‘Next’

4. Leave all of this to the default values and click ‘Finish’

That’s it, now you have created a new ODBC connection which you can use in AutoSQL!

Step 2: Setup an AutoSQL action list

When you open Autosql, go to ‘Get DSN’ and choose the earlier created ODBC connection

Put in a query and click ‘Test query’ to see if both the connection and the query itself work as expected.

Define your output options and click ‘Run Action’ to test it.

Before you can send emails through AutoSQL, complete the general settings and put in the SMTP server and credentials (if needed).

Step 3: Scheduling the action list to run at any time

In this example we use the standard available Windows Task Scheduler to schedule our AutoSQL action list. First click ‘Get Background Command’ in AutoSQL to put a command on the clipboard that can be used by Task Scheduler.
 

Now the background command you need for Task Scheduler is on your clipboard. Open ‘Task Scheduler’ and click ‘Create Basic Task…’

 
 
 Walk to the following screens to setup a schedule to run the AutoSQL action list every Monday at 8:00 AM.

1. Give your schedule a name

2. If you want to run it on a certain day, in our case Monday, set it to ‘Weekly’

3. Check the days it needs to run and set a time

4. Choose to ‘Start a program’

5. Paste the content of your clipboard in the ‘Program/script’ field. After pressing ‘Next’ it will suggest to split the program and argument, click ‘Yes’.

6. A summary is presented. Click ‘Finish’ to finalize your new schedule.

That’s it! Now you have setup the complete chain to run a query every Monday morning and send the results by mail.