August 22, 2011 at 1:37 pm
Hello all,
I have a weird situation in that it appears as though when a SQL Job run's it is pulling cached data.
My SQL job is pretty basic - it looks at a column in a table and checks to see if an entry has been made in that table (since the last run of the SQL Job) that matches a given string. If it does match it raises an error and then an alert is triggered on that error to send an operator an email notification.
DECLARE @RecordCount int
SELECT
@RecordCount = isnull(COUNT(1),0)
from
review r
inner join Member m on r.MemberID = m.MemberId
inner join ClientDetails c on r.ClientID = c.ClientId
, (
select top 1 ja.last_executed_step_date
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id
where
j.name = 'MySQLJobName'
order by
ja.last_executed_step_date desc
) job
where
isActive = 1
and r.Description like '%SearchString%'
and r.DateCreated between job.last_executed_step_date and GETDATE()
IF @RecordCount >0
BEGIN
RAISERROR (N'A Review has been created containing the text "SearchString" - the review should be verified', 010, 1 ) WITH LOG
END
If I run this code in SSMS it doesn't raise the error (which it shouldn't, as I have verified the data). If I put an entry in the table that satisfies the criteria, it does - so this logic works for what it is trying to achieve.
When I create the SQL Job and put the code in the exact same format as I am running in SSMS, it runs fine initially, but then when it finds a record that satisfies the criteria it creates the error (as expected), but then every subsequent run triggers the event also.
From my horrible throw together query it looks about right, in that it is checking that the record was created between the last_executed_step_date and the current date. Especially considering if I take the code and run it in a window it doesn't create the error, but when the next scheduled run of the SQL job kicks off, the alert is triggered and the notification is sent out even if there is no record that satisfies the criteria.
If I remove the SQL Job and recreate it (or reboot the server), it works fine - until the next triggering record is entered.
Can anyone let me know what in the world might be happening here?
Thanks in advance
Regards
Troy
August 22, 2011 at 2:36 pm
I wonder if it is a timing thing with the job.
have you tried changing
and r.DateCreated between job.last_executed_step_date and GETDATE()
to
and r.DateCreated >= job.last_executed_step_date
Also, are you using getdate() everywhere. You don't want to be using getdate() in one place and getutcdate() in another. The time offset could be issue.
August 22, 2011 at 2:44 pm
Hi Neal, thanks for the response.
I am all for simplification so have put that suggestion in place (using >= rather than BETWEEN), but alas no difference.
And yes I always use getdate().
Any other thoughts - please fire away, I am feeling like a sponge and will soak up any thoughts you have.
Cheers
Troy
August 22, 2011 at 2:50 pm
Let me ask this, how are you firing the notification event?
I don't think your RAISERROR severity is high enough to cause the job to "fail" setting the alert to the operator. I belive it has to be 16 or higher and you have it set to 10.
August 22, 2011 at 2:55 pm
The SQL agent raises the error
There is a SQL alert setup, that notifies the operator
The issue is certainly to do with the SQL Job (rather than the alert) because I can see the respective error in the logs which map directly to each run of the SQL job.
Slightly perplexed to say the least!
August 22, 2011 at 2:55 pm
I don't think your RAISERROR severity is high enough to cause the job to "fail" setting the alert to the operator. I belive it has to be 16 or higher and you have it set to 10.
August 22, 2011 at 3:02 pm
I don't want the job to fail though - I only want the error to be logged so that the alert can pick it up - which it does fine. So the error level I am happy with. The operator is being notified, so that part is working fine - the fact they are continually being notified (after the first instance of a matching record) is the problem.
The problem is that the script when run within the SQL job works fine until the point where it finds a record that triggers the raiseerror; from that point after (until the job is recreated or the server is restarted) the script will continue to hit the raiseerror part. Running within SSMS it works as expected.
August 22, 2011 at 3:07 pm
I understand. The issue has to be with the setup of the alert, the code is clearly working as it is suppose to or it wouldn't be logging the information.
August 22, 2011 at 3:28 pm
Well yes and no.
The code is working up until the point after it first raises the error; for every run of the code thereafter it creates the error again (which it should not) and thus creates a false positive error. Hence why it appears as though it is using some form of caching or something.
The SQL Alert itself seems to be working fine; so I believe this is setup okay.
I cannot recreate this using SSMS however - which is weird, if the same code hits the raiseerror part of the script in the SQL job I would have expected it to hit the same part of the script in SSMS - and when I recreate the job (or restart the box) everthing goes back to normal....until the next error is generated...
August 23, 2011 at 8:06 am
I would try to put in a bpc or something to bulk out the data to a file. This way you can see what data is being returned.
August 23, 2011 at 1:11 pm
yeah it's pretty weird though huh!?! I might dump the concept into a trigger instead and see if that suffers from the same problem....
although then I wonder because I am calling raiseerror will there be permission issues around that...hmmm time will tell....
In the interim, if anyone has any other ideas please let me know.
Cheers
Troy
August 23, 2011 at 3:38 pm
well after making it a table level trigger, everything is working fine now. I have removed the SQL Agent Job and the world is nice and calm and returning expected results.
Still weird though - can't explain why, but at least the workaround works.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply