brownp (9/1/2009)
I was able to clean the text and implement the solution. It runs great on our 4cpu dev server. I did notice that when the SQL you pass to the ‘sp_exec’ procedure produces an error you get no error message returned to you in the SSMS environment. Any work around for this other than copious logging?
Great to hear that you like it. You can use the following query to monitor all the sqls' running status. The error should appear in the [return_msg] column if there is any.
select * from pmaster.dbo.exec_queue
Basically the tool can be imagined as to "create many hidden SSMS windows", so I simply save all status in that table. As for workaround, you might change the pmaster.dbo.p_exec_wait stored procedure to print errors or raiserror, etc.
The sp_exec_wait is provided for the programming convenience. Sometimes you don't need it. For example, weeks ago I used the tool to rebuild all index of our huge database in 4-way parallel (not the meaning of execution plan parallel but the scripts parallel). I disabled the whole SQL Agent service in case any job got started accidentally, then I simply sp_exec all the rebuild sqls and used the following query to monitor their running.
select *
,wait_or_work=convert(varchar,dateadd(ss,datediff(ss,isnull(worker_start_time,send_time),isnull(worker_end_time,getdate())),'00:00:00'),114)
,sql=(select top 1 log_msg from pmaster.dbo.exec_log l(nolock) where l.exec_queue_id=q.exec_queue_id order by log_id)
from pmaster.dbo.exec_queue q(nolock)