March 31, 2003 at 3:07 pm
I'm working on something that loops around looking for specific occurances. Once it finds an occurance I want to pass that information to another process that does some "stuff" to it, meanwhile I want the main loop to continue on without waiting for a reurn. I've tried osql using xp_cmdshell, but xp_cmdshell waitd for a return. I'm doing the main loop in QA right now, with an SP for the called process.
Any suggestions would be Great.
Thanks in advance.
- Vega
April 1, 2003 at 12:17 am
You could write the info needed to perform the 'stuff' to some log table, then have either a trigger on that table or some job perform whatever is needed based on that info.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 1, 2003 at 5:23 am
Most common way is as Chris indicates, put what you need done in table (queue), then run a job periodically or have an insert/update to the table run sp_start_job. Which of those two you use depends on the volume, the latter being better for very low incidence tasks since it uses fewer resources.
Andy
April 1, 2003 at 5:45 am
I do this extensively - I have a number of jobs that check a table for an entry once a second continuously against a table with 155k rows and the overhead is negligble. On each record I have a status field (char 1) with a status of S - scheduled, I - In Progress or C - Complete. This is indexed and the job checks for rows with a Status if S. As this check is only an indexed read it is very quick hence no noticible overhead.
I'm not sure that a trigger would actually work. I believe that the trigger happens in process rather than out of process so that SQL Server can rollback transactions if necessary. If someone knows different then please let me know.
Jeremy
April 1, 2003 at 8:35 am
WOW, how'd I miss that! Thanks everyone; I'm going to go pull my head out of my ... have some more caffeine and get this thing done! Thanks everyone for your help.
- Vega
April 1, 2003 at 4:35 pm
Jeremy - I have the same understanding. I do not believe a trigger will work for this process. Other than that, the queue table works great.
Guarddata-
April 2, 2003 at 12:04 am
Jeremy, you're absolutely right. However, depending on your needs you might actually want to roll back if this produces an error. Normally not of course, and in those cases you can kick of an out-of process activity such as a job started with sp_start_job as Andy suggested. Normally I use the same approach as you though.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 2, 2003 at 7:22 am
The method of choice we used was to create and run a job that deletes itself when done. Creating and running a job spawns another thread and the main process does not wait for a result. It's doing exactly what we want it to do, although not as elegantly as we wanted. I cannot think of any ramifications of doing this, can anyone else?
- Vega
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply