March 4, 2011 at 3:43 am
Hello,
I have written a simple piece of code to send an email when there is a certain job running using a SQLAgent job. However, i am getting some funny results from the logic which i cannot explain!!
The code is:
-----------------------------------------------------------------------------------------------------------------------------
SELECT * INTO JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @execution_status=1')
declare @results numeric
set @results = (select count(*) from jobinfo where [name] not like '%hbu%' or [name] not like '%populate%')
if @results > 0
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'DBMail Alert',
@recipients = 'someone@domain.com',
@query =
'
select distinct a.name, b.description, c.step_name
from msdb..sysjobs a, jobinfo b, msdb..sysjobhistory c
where a.job_id=c.job_id
and a.name=b.name
and c.step_name <> ''(job outcome)''
and a.name not like ''%hbu%''
and a.[name] not like ''%populate%''
',
@subject = 'Running Job',
@attach_query_result_as_file=0,
@query_result_no_padding = 1;
end
-----------------------------------------------------------------------------------------------------------------------------
I know the code isnt very clever or gives any pretty results....but its the actions of the code i am struggling to understand. 99% of the time there should be no entries in the table....so should rarely get an email.
However, If i execute the code within a SSMS query window i dont get an email. If i copy the code into a SQLAgent job step i get an email. If i wrap the code into a stored procedure and execute it from a query i dont get an email. If i execute the sp from the job step i get an email!!
Can anyone explain this....is there some weird job logic or difference i am missing??
Thanks
LilyWhites
March 4, 2011 at 4:17 am
If think you've made the classic mistake with NOTs and ORs:-
set @results = (select count(*) from jobinfo
where [name] not like '%hbu%' OR [name] not like '%populate%')
should probably be
set @results = (select count(*) from jobinfo
where [name] not like '%hbu%' AND [name] not like '%populate%')
March 4, 2011 at 4:23 am
Hi Captn Scarlett!
i dont think i have....there can be a number of admin jobs that run around 7am which i dont want to report on as they have their own alerting process....this check is just to pick up a number of application-related jobs that run on the server which should not be running after 7am.
The admin jobs can have either the word 'hbu' or 'populate' in the title but not both....and i dont want either of them to be reported on....so i think i am correct?!?
Owww....i got all excited that someone has answered my question :p
thanks for posting tho 😀
March 4, 2011 at 4:29 am
wait....i apologise!!!!
thought i would test it....and you are right!!
now i need to figure out why this difference....althogth both ways return no results....cause this to act in this way!!!
Your help is much appreciated!!
March 4, 2011 at 4:36 am
Your strange/inconsistent results may be down to the fact that your IF statement had the incorrect OR in the where clause, but inside the body of the IF statement, you were using the correct AND in the where clause.
March 4, 2011 at 4:52 am
Hi Ian,
I have just had a nice conversation with someone about advanced logic and i think i understand!!
To explain for any others that may be confused....and to check my findings:
when you have 2 conditions as i did there are 3 possible results:
TRUE & TRUE
TRUE & FALSE (and in reverse)
FALSE & FALSE
When using AND the "TRUE & FALSE" gives a final result of TRUE (in my case)
When using OR the "TRUE & FALSE" gives a final result of FALSE (in my case)
thats where it is falling over when coming to the IF clause!! (if i am correct)
however, can you tell me why it runs differently in the query window compared to the job?? that bit i havent figured out yet!!
Thanks again
LilyWhites
March 4, 2011 at 5:04 am
Is it your own job that is being found and therefore generating the email, which obviously isn't running when you execute it from SSMS?
March 4, 2011 at 5:09 am
no i did have that issue before which is why i added the %hbu% clause to the select and altered my job name to fall under that clause
i was told it is something to do with the way the overall TRUE and FALSE answer is generated when applying the count to the results....i assume it goes through both "not like" conditions before counting rather than counting for each "not like" condition individually and then correlating the results??
im pleased its working now and have rolled out the new job....i think i might be able to accept this without knowing truly how it works!!
thanks again 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply