brilliant solutions for JOBS, Alerts and SQL Mail 1

[aomara]

I just want to use both JOBS, Alerts and SQL Mail to make brilliant solutions as follows:
1) the manager should be alerted with the new requisitions from his employee every day.
2) I think of the a job that select count of requisitions where the requisition date is (getdate()-1).
3) if the count of requisitions is >0 this send email to the manager.

I succeeded to send email whenever the job sucess, but my problem is the statments is always evaluated to success as soon as it returns any value.

How to make the job fails if the count of requisitions is zero ?

I can accept any other solutions that can apply this Idea.

Many thanks.

 

[Catadmin]

There are a couple of options. The ones I have in mind are related to SQL 2000 and haven't been tested in 2k5, but they should work.

1) Disable the job from within the job. Example:

Declare @DisableJob char(125)

Set @DisableJob = 'OSQL -E -SY0185-DBS0155-S -dMSDB -Q"Exec sp_update_job @job_name = Delete_Apacs_Backup_Files, @enabled = 1"'

Exec master..xp_cmdshell @DisableJob

2) Set up an SMTP job step, or set it up within your code, to send an email saying there were no requisitions. If you set it up as a job step, you can return an error (see RAISE ERROR & @@ERROR in Books Online) and use code in the Advanced tab of the previous job step to goto this step on failure.

SMTP Code Example:

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
	@FROM	= N'credit_technology_usd@allstate.com',
	@FROM_NAME	= N'SQL Server',
	@TO		= N'<managers email>',
  	@priority	= N'NORMAL',
	@subject	= N'No Reqs',
	@message	= N'The job failed.',
	@messagefile	= N'',
	@type		= N'text/plain',
	@server	= N'<server mailaddress>'
	
select RC = @rc 
go

Does this help??




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[aomara]

First,
I would like to thank you for you interest.

Second,
Actually, I tried different way by creating database procedure to check if there is a requisitions or not, and if any, it send the email.

Then I reformat the solution by creating a table with columns the same as the XP_Sendmail parameter and 2 condition colmns so I enter the data in Table, then the procedure read this table and send the email, so it can be dynamic procedure.

I will try to post this solution (Table and Procedure) in details as soon as I test it with diffirent cases.

Also I will try to test your solution as to have the benefit of it.

Many Thanks.