#AcumaticaTnT Running SQL Code automatically on a Schedule

If you're interested in scheduling SQL code to run on a schedule, then you'll like this post.

You can run any SQL code that you want, but I'm keeping things simple by assuming that all of the SQL logic is in a Stored Procedure, then I'm executing the Stored Procedure on a schedule.

Rather than build a scheduler into AugSQL, we decided to rely on the existing scheduling capabilities in Business Events.

1. We need to create a Generic Inquiry that has only 1 record in it. The reason is that we only want to trigger the schedule to refresh once. The GLSetup table only has one record in it so let's use that in our Generic Inquiry.

2. Let's create a Business Event that uses the Generic Inquiry that we just created. Setting up the Business Event and the Automation Schedule is pretty straightforward:

3. Now for the magic. We simply add an Import Scenario to the Business Event. The second screenshot is very simple, but it's very powerful and the "magic" is highlighted with the red arrow in the second screenshot:

That's it? Yep, that's it! Simple, but powerful.

If we wanted to, we could add more Import Scenarios to execute additional SQL code snippets on the same schedule.

I'm currently using this technique at a client to essentially build a Data Warehouse within the Acumatica database, with custom tables that store summarized data. I use a Stored Procedure to refresh the data in these tables on a nightly basis. So far, the longest running SQL snippet takes about a minute and a half to run, but it has run reliably. I haven't tested longer-running queries yet to see if they timeout at a certain point.