Solved

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

Posted on 2008-06-25
2
1,215 Views
Last Modified: 2012-05-05
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
Comment
Question by:hennessym
2 Comments
 
LVL 12

Accepted Solution

by:
jgv earned 250 total points
ID: 21868224
Include the "set nocount on" in the query passed to the send mail routine

@query = 'set nocount on select top......'
0
 
LVL 1

Author Comment

by:hennessym
ID: 21869194
Thanks - that's exactly what I needed.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now