schedule a sql query in 3 steps

To automate your queries, AutoSQL needs to connect to your database. To make this available for all databases, AutoSQL uses the ODBC standard. Almost all databases have an ODBC driver which can be used by AutoSQL.

The steps below describe the process to setup a connection to your database, define the output format (eg Excel, CSV or HTML) and send it by email. This can then be scheduled with Windows Task scheduler to never have to look at it again.

To start, first download AutoSQL and start your free 30 day trial.

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.