Alerts and sending E-Mails

[Liam1]

Hello,

I have a small SQL Server running that services some users.
I wish to set up an alert when an extarnal feed fails or a stored Procedure completes etc.

In order to inform me of when an extarnal feed fails, I need to put a value into a table.

When a value is placed in this table I want an error e-mail sent to me and to put a value in another table.

I have set up my Message, and set up the alert, however when I execute the commant RaisError, all I get is the out put. I have also asked the alert to trigger a job, so I know it is something to do with the Alert.

The raise Error cmd doesn't seem to be triggering the Alert.
Am I missing something?

Insert into Error_Log_Table "Job 1 Failed"
RaisError (50001, 0, 1)
GO
Call Alert:
Executes Job -
Sends Mail - Error Msg

Any ideas?

Thanks in Advance,

Liam.

 

[TJRTech]

You would use the SQLMail facility and xp_sendmail for sending the mail, but I and others would recommend HIGHLY against sending the mail from a trigger or inline as part of your normal processing.

Instead of doing it inline in your code or with a trigger, create a mail queue table that includes columns for each of the mail fields you might have (subject, body text, To, From, etc). Make sure to add a PK/Identity column. Then, in your error handling of your code simply insert a new record into your mail queue table.

Then, to send the mail, develop another SP that you will automate via SQLExecutive (the scheduling component). Run it as often and as many times a day as you need. The SP will simply work from the mail queue table, using a cursor or a while loop, grab one record, process it by sending the email, delete the record, than continue until completed.

TJR