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 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
The easiest way to get a connection string is to build it from the relevant driver wizard. The installed drivers are listed like below:
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 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
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’
When you click finish, and the connection was succesful, the resulting connection string is inserted into AutoSQL:
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.
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
Now the background command you need for Task Scheduler is on your clipboard. Open ‘Task Scheduler’ and click ‘Create Basic Task…’
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.