Solved

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

Posted on 2008-06-25
2
1,231 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 41
Error message when scheduling a job using a linked Server 12 67
Delete from table 6 47
How to use TOP 1 in a T-SQL sub-query? 14 46
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 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