• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1358
  • Last Modified:

"SET NOCOUNT ON" is not working in my stored procedure

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:

USE [Cyfast_Stockaid]
GO
/****** Object:  StoredProcedure [dbo].[NewDataCheck]    Script Date: 06/25/2008 13:04:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[NewDataCheck] AS

SET NOCOUNT ON

if not exists (select top 1 * from mydb.dbo.mytable where datediff(ss,UpdateDate,getdate()) < 86400)      
      begin            
            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'myemail@mail.com',                         
            @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';
      end

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:

-----------------------
LastUpdated: 06/20/2008

(1 rows affected)

Does anyone know how to solve this?

Thanks in advance.

0
hennessym
Asked:
hennessym
1 Solution
 
jgvCommented:
Include the "set nocount on" in the query passed to the send mail routine

@query = 'set nocount on select top......'
0
 
hennessymAuthor Commented:
Thanks - that's exactly what I needed.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now