I have a script that e-mails me if new records have been added to a table within the past 24 hours. The body of the e-mail indicates when the most recently added record was inserted into the table:
/****** Object: StoredProcedure [dbo].[NewDataCheck] Script Date: 06/25/2008 13:04:47 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER procedure [dbo].[NewDataCheck] AS
SET NOCOUNT ON
if not exists (select top 1 * from mydb.dbo.mytable where datediff(ss,UpdateDate,getdate()) < 86400)
@recipients = 'firstname.lastname@example.org',
@subject = 'Database alert: No new Data within past 24 hours!',
@query = 'select top 1 ''LastUpdated: '' + CONVERT(VARCHAR(10), UpdateDate, 101) from mydb.dbo.mytable order by UpdateDate desc',
@importance = 'high';
This works as designed except that I see the number of records affected in the body of the e-mail, seemingly indicating that SET NOCOUNT ON is not working:
(1 rows affected)
Does anyone know how to solve this?
Thanks in advance.