February 25, 2009 at 8:20 am
Is there a way to run a TSQL command, but not have it block processing of other commands that follow? I want to put a stored procedure call in the middle of a block of SQL code and have it run, but not cause the statements that follow to wait for it to finish. Here's a contrived example:CREATE PROCEDURE Schema.Procedure
AS
BEGIN
SELECT something FROM sometable
--I would like to have the procedure below run on it's own
--'thread' and have the select below it execute almost
--immediately following the SELECT above.
EXEC ThisProcedureWillTakeAwhile @myparameter
SELECT something FROM sometable
ENDI've thought of a few ways to do it, none of which I particularly like:
--Have the SP start a job that contains the code that needs to be run (the job start is fast, the job itself can take as long as it needs and won't hinder the execution of the rest of the code)
--Use the service broker (This is the right solution, but I don't want to add the overhead when I only have this one need. And I need to learn the service broker, which would be a new topic for me w/ the associated learning curve and maintenance).
--Have the SP insert a record into a log table. Have a second procedure execute regularly and watch that table, taking action when it see new records (or... have a internal loop - WHILE 1=1 and a WAITFOR DELAY at the bottom of the loop so it doesn't consume all resources)
Thanks,
Chad
February 25, 2009 at 8:51 am
I'd go for job or service broker.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 8:54 am
Job - granularity is only every minute, so be aware there.
SSSB is probably the best choice, and it's not that hard. Not sure how you'd get the results back to the client, probably need a Q they can read.
You could, and I highly, highly DO NOT recommend this, but call xp_cmdshell with a "start" to spawn off a thread.
February 25, 2009 at 9:16 am
Steve Jones - Editor (2/25/2009)
Job - granularity is only every minute, so be aware there.
The job doesn't have to be scheduled. I did something like this with replication where the post-replication script just runs sp_start_job 'JobName'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 9:56 am
First run I combined the loop and job options, kinda taking a page from the replication notebook. I created a stored procedure with the loop and delay so that it would check every 2 seconds (more frequent than the 1 minute job granularity), then scheduled a job to run every minute that calls the sp. What happens is the job starts and the sp runs forever (like the replication jobs), but if the job ever dies for some reason, it will auto-heal by starting up again within 60 seconds. I created another job that stops the first job in the middle of the night when noone is around, just cause having a sp running forever seemed a little spooky.
I don't need to give any results back to the client - I'm actually injecting my piece into a 3rd party's process, so I'd rather they were not impacted and I'll create other systems to handle recovery if my piece fails.
So... if SSB is easy to learn and set up... how is the maintenance, monitoring and stability? When I first dove into replication years ago, there was a lot to learn about how to detect failures and fix errors, not something you would want to learn and deploy without a long beta period to make sure you had done it right. Is this really a fire-and-forget deal, or do I need to do the long due dilligence like I did when starting with replication? I need something now, and the job/loop I understand and can easily monitor and fix. Assume I'm reasonably competient... 😉
Thanks,
Chad
Edited: removed color formatting that didn't work.
February 25, 2009 at 10:00 am
The job doesn't have to be scheduled. I did something like this with replication where the post-replication script just runs sp_start_job 'JobName'
I thought about that, but was worried about a job-start call coming just as the job was finishing and not having it start up again. The code within my procedure handles simultaneous calls (will do the work for 2+) as long as the SP is still running when the call comes in, but if the sp is done but the job hasn't closed out before another call comes... I was worried that the call would be lost.
Chad
February 25, 2009 at 11:51 am
Chad Crawford (2/25/2009)
The job doesn't have to be scheduled. I did something like this with replication where the post-replication script just runs sp_start_job 'JobName'
I thought about that, but was worried about a job-start call coming just as the job was finishing and not having it start up again. The code within my procedure handles simultaneous calls (will do the work for 2+) as long as the SP is still running when the call comes in, but if the sp is done but the job hasn't closed out before another call comes... I was worried that the call would be lost.
Chad
believe me it does not work if the job is still running.
There are many methods of doing this. Definitely SSSB is one of them and stability is good but the learning curve is steep!
Here is one of the possibilities http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/[/url]
* Noel
February 25, 2009 at 4:47 pm
That's what I was afraid of. I know if the job is still running when the schedule wants it to start again, it doesn't kick off right after finishing - it waits for the next scheduled execution and runs then. I figured using start_job would be about the same.
I'll take a look at the link - it looks promising, but I haven't had time to dig through it yet. Thanks Noel.
Chad
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply