Custom job alert notification

I have set up a sample job which is select count(*) from [SR]
It will give no of records in table SR as say 120.

Then I get alert message on netsend with the job run status.

Is there any way I get the no of records i.e. output of select statement above also in the alert message?


more ▼

asked Jun 14, 2010 at 05:09 AM in Default

spareus gravatar image

24 1 1 2

added SQL 2000 version to my answer following your comment.
Jun 18, 2010 at 06:19 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

This is a bit more than you probably need but it illustrates the flexibility that Kev mentions. Create a job with a TSL step, paste this in and add your email address. Providing you have an adventureworks database and SQL 2005 or higher you should get an email sent to you with the count that you need

USE adventureworks


SET @tableHTML = N'<table>' + N'<tr><th>Count</th></tr>'
    + CAST((SELECT td=COUNT(*) FROM [Person].[Address] AS a
             XML PATH('tr'),
           ) AS NVARCHAR(MAX)) + '</TABLE>' ;

-- send email
EXEC msdb..sp_send_dbmail @recipients = N'your.email@address',
    @subject = N'Adventureworks - count of address',
    @body = @tableHTML, @body_format = 'HTML'        


When you start to use TSQL rather than netsend you get much more options. This example is a variation on the example in Books OnLine.

[Edit - following advice that we are working with SQL 2000]
Using XML for formatting is out so we can only use the xp_sendmail procedure detailed here : http://msdn.microsoft.com/en-us/library/aa260697%28v=SQL.80%29.aspx

You need Outlook installed on the server and an Outlook Profile created for the account that is running SQL Server service. Then you can write a step that does something like

EXEC xp_sendmail @recipients = 'operator@email.com', 
   @query = 'SELECT COUNT(*) FROM [Person].[Address] AS a',
   @subject = 'SQL Server Report',
   @message = 'The [Person].[Address] table count:',
   @attach_results = 'TRUE', @width = 250

Hope this is what you need, or close enough to get you there ...

more ▼

answered Jun 14, 2010 at 05:37 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Fatherjack, Thanks for your help. But I am using SQL 2000 and can not use email option due to some restrictions.

Is thare any other options? Regards,
Jun 18, 2010 at 06:06 AM spareus
OK, I have added a new section in the answer for SQL 2000
Jun 18, 2010 at 06:13 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Not with netsend as it is 'out-of-the-box' - you'll have a lot more options available to you if you decided to use email instead.

The other option would be to have a final step in the job that then executes a script to netsend you with whatever message you wanted.

more ▼

answered Jun 14, 2010 at 05:25 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 14, 2010 at 05:09 AM

Seen: 2153 times

Last Updated: Jun 14, 2010 at 05:11 AM