Download the latest version of AutoSQL and start your 30-day free trial. The trial is fully functional for 30 days, so you can test your use case without limits
QUERY AUTOMATION
Make Your IT Work
Query automation is the process of removing the human interaction from executing SQL queries, storing the results in a specific format and distributing it to the end user. Data stored in a database can be retrieved using SQL queries. When executing queries, the results are returned by the database.
This process is normally triggered by an end user requesting the information through a front end application. In that case, part of the query automation is done by the application already, but the trigger is still from the user.
Start with AutoSQL in 3 steps
Learn how to use AutoSQL to automate and schedule any SQL query. Setup a connection to any database, provide a SQL query and schedule it to send a daily email with the results attached.
Pushing queries results
In other cases the end user doesn’t want to trigger the executing themselves, but have that automated as well. A scenario would be a SQL query which rarely has results, and the user wants to get notified if it does, for example by receiving an email with the results in an Excel attachment.
An example would be exception monitoring, where you write a query to see if you have an online order process is in a blocked state. Read the customer case Woodland Hills Wine Company uses AutoSQL to prevent lost orders.
Keeping history could be another scenario’s where you want to store a daily result.
In this scenario, full SQL query automation is needed to have the query run daily or hourly and have the results being written to the network and sent to the user.
Scheduling queries for automated execution
Automating the executing of SQL queries can be handled by any client tool which can be scheduled.
Typical solutions are in place by the database vendor already. Like Microsoft SQL server has the SQL Server Agent and MySQL has the MySQL Event Scheduler. These solutions do require specific authorizations from your DBA as they run on the database server itself.
The advantage of using tools like AutoSQL is that they run outside of the database itself and can be scheduled by anyone using the Windows Task Scheduler.
Storing the query result in a specific format
The next step in query automation is having the result stored in a specific format. Typically the required output format is CSV or Excel.
Not all tools have the ability to output to real Excel files or functionality is very limited. AutoSQL does output to Excel and also has the ability to write to specific sheets, cells and using template. Read more about using templates with AutoSQL in the support article ‘Using an Excel template for advanced formatting‘.
Distribute the query results by email
Once the results are stored they need to be distributed to the end user. This can be done by saving it to a network location, Sharepoint or other place where the user will see it. Really pushing the result to the user could be done by sending it by mail.
Including the results in your email can be done by attaching the stored results, or directly showing them in the email body. The latter can be done by AutoSQL and you have the ability to limit the number of rows, to prevent 1000’s of rows to end up in an email body. Read more about that in Using placeholders.