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 '10 at 05:09 AM in Default

spareus gravatar image

spareus
24 1 1 2

added SQL 2000 version to my answer following your comment.
Jun 18 '10 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 '10 at 05:37 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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 '10 at 06:06 AM spareus
OK, I have added a new section in the answer for SQL 2000
Jun 18 '10 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 '10 at 05:25 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x85
x11

asked: Jun 14 '10 at 05:09 AM

Seen: 1920 times

Last Updated: Jun 14 '10 at 05:11 AM