January 2, 2012 at 2:48 pm
OK.. I have been banging my head on the wall for a week now and haven't been able to figure this out yet.. Most likely its something simple and I'm going to feel like a fool later...
I'm trying to create a sort-of Job Scheduler in T-SQL. I have a Job Queue table, and I have created a stored procedure that runs a WHILE loop which reads the table for a job, and if it doesn't find one does a WAITFOR and checks again.. The jobs are specific, so the stored procedure is hard-coded, I just use the JobQ to trigger the event. The scheduler is reading the JOB QUEUE, and updating it fine... but for some strange reason the stored procedure it executes is running, but it doesn't return any results.
When I execute the stored procedure call manually from SSMS it works fine... but when its called from my Job Scheduler Proc, I don't get any results. To make sure it was executing I inserted logging statements in the proc being called and confirmed they are being written, but the main task the procedure is suppose to do isn't happening.. No errors codes are being raised.. and I'm dumbfounded.
I have put the EXEC call in a TRY.. CATCH block, and that isn't picking up any errors either....
Can anyone give me a clue? Am I missing something?
January 3, 2012 at 4:03 am
We're not behind your shoulders and can't see your screen. 😉
Can you post your code (or a meaningful part of it)?
-- Gianluca Sartori
January 3, 2012 at 7:31 am
You've got a query that is looping reads on a table waiting for inserts... You might just be blocking one process or the other. I'm not sure that's the approach you ought to take. If you need to respond to inserts into the table, then add a function to the insert process of the table, or, use a trigger.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2012 at 8:05 am
The calling program is in a loop - WAITFOR cycle. When it finds a records in the JobQ with a execute time <= getdate() then it does a set of commands:
roughly the calling procedure is like this:
While .....
set rowcount 1
select @now=getdate()
select ... from JOBQ where Status="QUEUED" and RUN_TIME <= @Now
if <job not found>
....
else
begin
Update the status of the Job to "Processing"
Insert a Log entry that its running a job
EXEC <procedure_name> <parameter1>, ... <parameter8>;
Insert a Log entry that it has completed the job
end
Loop.
Its can't be blocking.. I am testing with only 1 job in the table.
The procedure being called is too large to upload here.. its a couple thousand lines long.
Essentially it starts off by writing the parameters into a log table to confirm the date/time the procedure ran, and what parameters were passed to it.
(That works fine.. I can read those records when its finished...)
Then I create a # temporary table.. populate and update it from a dozen different tables...
then at the end of the procedure if parameter 8 is a 'N' I return a results set, but if its a 'Y' I insert the records from the # table into a permanent User Table.
Regardless of which parameter I pass, the called procedure will record the parameters in the log table, but never returns any data...
My TRY... CATCH block is isn't catching any error...
and if I execute the proc manually from SSMS with the same parameters it works fine...
Any ideas?
January 3, 2012 at 8:51 am
You could try print statements in the script and proc and then capture the output from the job to see what you get to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2012 at 9:02 am
Altough you can see a resultset when you run a sp, the sp always returns an int value, usually zero if there isn't execution errors.
If you need to handle a dataset generated in your sp, you can assign it to an ouput parameter of table type:
declare @t table (col1 int,
col2 int,
col3 nvarchar(50))
exec spRead 1, 5, 23, @t output
select * from @t
Other option can be insert the result of your select statement into a temporary table.
January 3, 2012 at 9:26 am
Thanks for all the help everyone...
After a few hours now of step by step tests... I found the problem...
I was not aware that the scope of the "set rowcount" statement carried through from the calling procedure into the one being called.
Once I set the rowcount back to 0 called the proc, and then set it back to 1 afterwards... everything ran fine.
Like I thought.. a stupid mistake.
January 4, 2012 at 12:38 pm
SET ROWCOUNT has been deprecated. You should use TOP instead to get a single row. It also won't cause any problems with other procedures.
Todd Fifield
January 5, 2012 at 7:29 am
Just in case you want to scale this out in the future to have multiple "queue-poppers" note that you can use the READPAST hint to allow the queue SELECT statement to skip past rows that other queue-poppers may be locking.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply