SCHEDULE A SQL QUERY IN 3 STEPS

Let's Get Started

The ODBC standard exists for a long time and is supported by almost all databases. AutoSQL uses ODBC drivers to execute queries on any database. From the AutoSQL GUI you can specify a connection string directly, or build one from any of the installed driver wizards. If you already have a DSN created from the ODBC Data Source manager, you can also extract the connection string from that.

Step 1: Setup a connection to your database

Before you start you need to download AutoSQL. If you click the button below you will download the 64 bit version

Download Windows 64-bit installer

The easiest way to get a connection string is to build it from the relevant driver wizard. The installed drivers are listed like below:

Setup your connection string

If the driver you need is not listed, first install it on the computer or server you run AutoSQL. A list of ODBC driver downloads can be found below for the most common databases (SQL Server, MySQL, Oracle, PostgreSQL , DB2): ODBC Drivers.

When you click the ‘Build connection string’ button, it will come up with the driver wizard to setup a connection to your database. The example below will continue with SQL server as an example.

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

Provide SQL server hostname

1. Select the SQL server you want to connect to. The drop down menu doesn't always work, in that case you should manually put in the hostname of the database

Provide authorization

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

Choose default database

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

Finish connection

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

When you click finish, and the connection was succesful, the resulting connection string is inserted into AutoSQL:

Resulting connection string

The password is not always copied, so don’t forget to re-enter that. Hit ‘Test connection’ to get a final confirmation that it’s working.

Step 2: Setup an AutoSQL action list

When the connection is establed, you can continue to the ‘Query’ tab and enter the SQL query that you want to automate or schedule.

Click ‘Test query’ to see if both the connection and the query itself work as expected.

Resulting connection string

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

Resulting connection string

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

Provide email settings

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

After you have saved your action list, you can schedule it. This can be done by clicking the ‘Schedule’ button. It wil help you to create scheduled tasks in Windows Task Scheduler.

Schedule the task

Now you will get a new dialog where you can edit or delete existing scheduled tasks for the current action list. You can also create a new one by clicking ‘Add new’.

Add new task

Use the options to create a new trigger for starting the background job. In the example below the task will run every monday at 02:00 AM.

Provide schedule with trigger

After defining the schedule, you will get a final screen for specifying the details of the scheduled job. By default it will get the same name as the AutoSQL job file appending with the current time. In this screen you can also change the security settings. This is important as it determines if the job can be ran on the background and which credentials are used.

Provide task details

If you choose the job to ‘Run wether user is logged on or not’ it will finaly prompt you for the password. This is standard for Windows Task Scheduler.

Provide task details

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