Raiserror in stored Proc that will notify users when records exist

I have been tasked with  creating a stored procedure in Sql Server that if the query brings back a value when executed at a specific time by a Sql Job a custom error will go out to specific operators.  Another Requirement is that i have to return the query in the email so that the operator can research the records.  If the query is executed and does not bring back any records then it ends with no notification.  Below is a query that i wrote that brings back an error if i run it but when i run it in a job the test notification does not send and I have yet to figure out how to return the query that i ran.

IF  EXISTS(SELECT 
				Count(distinct id),
                                                               orderdate
					    FROM [TestTable] ttbl (nolock)
        Where orderdate> getdate() - 2 
	    group by orderdate
having count(distinct id) > 1
	) 
	
	BEGIN
	RAISERROR(50031, 10, 0) 
End 

Open in new window

RiveraJdataAsked:
Who is Participating?
 
RiveraJdataAuthor Commented:
I was able to figure out a solution by creating a second stored proc that on failure of the Job it would execute the and query the MSDB database to get the query and the error that it bounces back and uses the calls the dbsendmail proc.
0
 
Vadim RappCommented:
Probably there's no need in raiserror, simply send email using xp_sendmail or sp_send_dbmail, depending on the version of sql server. Best is to send to mail alias and include in the alias the individuals you want. Also, consider putting that not in sql job but in the trigger on the table you look at, then it will be instant notification.
0
 
sammySeltzerCommented:
Well, I think you can leave in the job just like you are doing.

However, first of all, I don't see the message that goes out when a certain condition occurs.

I see numbers. Maybe ,you are translating the numbers to messages somewhere.

In any case, the way I have done these things in the past but my issue is that your requirements aren't very clear.

In my case, I will grab records that meet or does not meet certain conditions and store them in another table.

I called mine notifications table.

In this table, I have a flag called sent set to No by default.

All instances that meet or does not meet your requirements gets stored on this table.

When  a notification is sent, this flag is set to Yes so it doesn't get sent again until another record is stored there.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vadim RappCommented:
@sammySeltzer, I think what he expects, is this: he created an Agent job and configured it to send alert on job failure. If condition is met, he wants the job to fail.
0
 
sammySeltzerCommented:
Ok, I see my bad. I read it incorrectly.
0
 
RiveraJdataAuthor Commented:
Something i did not add was that i added a custom message in the sql alerts table above 50000.  I guess what i would like to know is How do i Return the query that i executed when executed and it meets the criteria and errors out.  also I created a test error and placed it in my test environment and setup sql mail with no success. Thanks for everything
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.