QUERY AUTOMATION

SQL automation means a query runs without anyone asking it to. Nobody opens a database client, pastes in a statement, exports the grid and forwards it on. The query runs on its own schedule, writes its results in a format someone can actually use, and delivers them to the people who need them.

Put another way, automating a query means handling three things that a person normally does by hand:

1. Running it — on a schedule, or often enough that nobody has to remember.
2. Formatting the result — a real Excel or CSV file, not a screenshot of a result grid.
3. Delivering it — to an inbox, a network share or SharePoint, without a covering email written by hand.

Miss any one of the three and someone is still doing the work manually.

The pattern most people actually want: only tell me when it matters

A daily report that arrives every day gets filed unread within a fortnight. The automation that earns its place is usually the opposite: a query that runs constantly and stays silent until there is something worth acting on.

Jon Morgan at Woodland Hills Wine Company had exactly this problem. Website orders occasionally got stuck in the staging tables and never became real orders. The database raised an error flag, but as he put it, “if no-one is looking, it gets lost pretty quickly.” His first attempt — SSIS exporting to a text file with a SendMail app — worked, but emailed him every two hours whether or not anything was wrong. Which is another way of not being told at all.

The fix is a query written to return no rows when everything is fine, and an automation that only sends when there is a result.

Bultsma Transport runs the same pattern on a fleet of 17 trucks. Their query reports any truck within a 5,000 km maintenance window, and runs daily. Most days it returns nothing and nobody hears from it. When a truck comes due, the office gets an email with the results attached as Excel — and once the maintenance is booked, the truck drops out of the query and the reminders stop by themselves.

Exception monitoring, expiry checks, stuck records, threshold breaches: they are all this one shape. A query that answers “is anything wrong?” and an automation that keeps quiet when the answer is no.

Where to run the schedule: inside the database, or outside it

Most databases ship with something that can run a statement on a schedule. Microsoft SQL Server has SQL Server Agent, MySQL has the Event Scheduler, Oracle has DBMS_SCHEDULER. If you have the access and the queries are simple, these are perfectly good.

The catch is where they run. They execute on the database server itself, so they need privileges your DBA has to grant, and changing a schedule means changing something on production infrastructure. In a lot of organisations that turns a ten-minute job into a change request. They are also built to run SQL, not to produce an Excel file and email it — you end up bolting SSIS packages or scripts alongside them, which is where Jon’s every-two-hours problem came from.

Running the automation outside the database avoids that. A client tool connects over ODBC with the same credentials a person would use, so it needs no new privileges on the server. AutoSQL works this way: it is scheduled with the Windows Task Scheduler, which most people can already use without asking anyone’s permission.

Getting the result into a format people can use

The output format is where most home-grown automation falls down. A CSV that Excel mangles on open, or a fixed-width text file someone has to reformat every morning, is not automation — it has just moved the manual work downstream.

For a lot of reporting, a plain CSV or a real .xlsx file is enough. Where it gets interesting is when the file has to fit something that already exists. AutoSQL can write into specific sheets and cells of an Excel workbook, so results can drop into a template that already carries the formatting, formulas and headings.

TSE Imaging use that in both directions. Their reports come out of SAP Business One’s SQL Server backend, and supplier master-data files go out as Excel built from a template — which suppliers fill in and send back, ready to be loaded straight into SAP. Robbert Langezaal notes that this “improved master data quality and that helps many of our internal processes.” Automating the report also fixed the data going the other way.

There is more on this in using an Excel template for advanced formatting.

Delivering it without anyone forwarding anything

Once the file exists it has to reach someone. Writing it to a network share or SharePoint works when people know to go and look. For anything time-sensitive, push it instead — send it.

An attachment is the obvious approach, and for a monthly report it is the right one. But an attachment is a file someone has to open, and for a short result that is friction for no reason. TSE Imaging found the opposite worked better: “The ability to have the results directly in the email body is great as well, this way people see the information directly and don’t have to open any attachments.”

Putting rows into the message body needs a limit, or one bad WHERE clause produces an email with ten thousand lines in it. AutoSQL caps the number of rows for exactly that reason — see using placeholders for how the results get built into the message.

Where to start

Pick one query someone currently runs by hand, and automate the whole path: schedule, format, delivery. Not a dashboard, not a reporting project — one query that somebody is tired of running.

If it is a monitoring query, write it so that it returns nothing when all is well. That single decision is the difference between an alert people act on and another daily email they filter away.