In my previous post I created a simple procedure to run multiple queries in parallel.
It was very simple and easy to use, but for more advanced production processes it is too simple.
In order to satisfy additional user requirements I added some functionality to that procedure.
The major changes I've made:
1. Added execution logging functionality. If table "tbl_Parallel_Execution_Log" is present in current database then execution will be automatically logged. By default it won't log executed query, but you can still record it if you specify "@LogQuery" parameter as "2". Log table definition is included into stored procedure just in case:
2. By Steve Ledridge's advise I've added SQL Agent Job deletion not as a separate step, but as job's property. Now by controlling "@delete_level" parameter you have a flexibility to delete a job on failure or success. However, with logging functionality there is very limited number of cases when you'd need to research failed job.
1. The stored procedure is provided "as is" and at your own risk. Do not run it in production before the comprehensive testing in Dev environment. 2. That method is very insecure. Whoever has rights to run that procedure can make a damage to your whole SQL Server. 3. Because SQL Agent job is self-deleting, you won't see any execution results of it. That means you have to do your own error handling. The most preferred way to wrap all executed queries inside of hard coded stored procedures with their own error handling. Later on I'll come up with more comprehensive version of that procedure to provide error handling and execution check.