Solved

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

Posted on 2008-06-25
2
1,244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading SQL 2005 Express to 2008 R2 Express 31 160
SQL Server 2012 express 24 42
Restrict result set 1 44
denied execute as 13 28
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 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