How to send email
-
hello,
Kindly teach me on how to send an email to a certain user if there are data in a specified table.
Let say, in a user table, there is one pending user that needs to approve. Then the sql will email the administrator to remind her that there is a pending job for him. So from time to time, if the user table still not empty. SQL will keep on reminding the administrator.
I was looking on the DTS package sending mail task but I am lost on how I will going to check the table if its empty or not.
I really appreciate your help. Thanks in advance.
-
One way of doing it is to create a stored procedure similar to what I did below that monitors the table. I will then create a job that calls the SP every x number of minutes.
CREATE PROCEDURE pr_CheckForDataInTable as
SET NOCOUNT ON
DECLARE
@AlertMessage VARCHAR(1000),
@MailSubject VARCHAR(100)
SET @MailSubject = 'Pending job'
SET @AlertMessage='There is one pending job waiting for you.'
if (SELECT COUNT(*) FROM TableToCheck WHERE Flag=0)>0
BEGIN
EXEC master..xp_sendmail @recipients = 'UsersEmailAddress',
@copy_recipients = 'YourEmailAddress',
@subject = @MailSubject,
@message = @AlertMessage,
@query = 'SELECT *FROM TableToCheck WHERE Flag=0',
@attach_results = 'TRUE',
@width = 300
-- Flag rows in TableToCheck here so that they don't get emailed again the next time
-- the job checks the table
END
SET NOCOUNT OFF
GO
Hope this helps.
-
Thank you so much. You are a big help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply