x

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?

Regards,
Spareus

more ▼

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

avatar image

spareus
24 1 2 4

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 voted first

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 GO

DECLARE @tableHTML NVARCHAR(max)

SET @tableHTML = N'<table>' + N'<tr><th>Count</th></tr>' + CAST((SELECT td=COUNT(*) FROM [Person].[Address] AS a FOR XML PATH('tr'), TYPE ) 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'

    GO

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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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

avatar image

Kev Riley ♦♦
64k 48 61 81

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x99
x13

asked: Jun 14, 2010 at 05:09 AM

Seen: 2574 times

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

Copyright 2016 Redgate Software. Privacy Policy