Fire and forget methods for SQL
So you need to automate some long SQL crunching process in the background? You don’t want slow code and you do not want the browser tied up. You need a fire and forget method. There are several interesting ways to accomplish this. Using ColdFusion 8 you can create a CFthread to hold the process, but we are not at that version yet. We also do not have permission to create CF schedules. I did not want to attempt to consume a webservice from SQL or do anything risky from a security point of view.
My first solution involved scheduling a SQL Job that checks every now and then in a table and fires off based on the values stored there. The problem with this is notifications of when the Job finishes.
Another solid method was to build an AJAX loading screen and use a CFFlush to give most of the page back but leave the long running process as a background part of the page. This also was painful, creating timed out pages and locked tables. More details on this solution at ColdFusionJedi.com.
The final solution I built is similar to the above, but with one interesting difference. I still populate the queue table with the variables I want processed. I still have the stored procedure set up to look in the table and do the work. The trick is to launch a Stored Procedure using the sp_start_job in a table trigger. Sp_start_job has a special quality that it does not need to return a status. Here is what the actual trigger looks like. Simple!
CREATE TRIGGER [dbo].[DoThisLongThing]
EXECUTE msdb..sp_start_job N’LongRunningJobName’ ;
I ran into two gotchas. They both relate to sp_start_job living in another db (msdb). The first error below is an indicator that the job or a child job does not have proper permissions. The database cannot see the job even though it exists and you get something like:
“The specified job_name (‘Your_Job_Here’) does not exist.”
And the other frustratingly cryptic problem:
“The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.”
To solve these, first attempt to authenticate as the web db account using Microsoft SQL Management Studio and perform an insert. This should narrow the problem down. If the web insert fails to fire the trigger with the above errors, here is a path to investigate:
- SQL Agent must be running
- The target db account needs the ‘SQLAgentUserRole’
- Make the target db account owner of the job
If you still end up with errors look into the following:
- Explicitly grant the EXEC permission to the target account
- Set the target database as trustworthy from msdb
- Verify the current version of MDAC on SQL and on CF server