I need to remove "Rows affected from query in send mail"

• Sgar...

  SSCarpal Tunnel

  Points: 4409

  More actions

  May 6, 2015 at 3:45 am

  #296704

  Hi I need to remove "rows affected" text from results as shown below from posted Sp. i am using set nocount on but its not working as expected.

  Create Procedure DailyCheckList

  As

  SET NOCOUNT ON

  Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)

  set @dt= convert(varchar(20),GETDATE(),107)

  Set @EmailSub='Daily Checklist for '+ @dt

  exec msdb..sp_send_dbmail

  @Profile_name = 'XXX',

  @recipients ='XXX',

  @subject =@emailSub ,

  @Body = @Msg , ------@ReturnMessage

  @query = 'select * from [DailyCheck_DiskSpace]; Select * from [dbo].[DailyCheckFailover]; select * from [dbo].[DailyCheckFailedJobs]' ,

  @body_format = 'HTML'

  Sagar Sonawane
  ** Every DBA has his day!!:cool:

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 6, 2015 at 3:59 am

  #1795874

  Use SET NOCOUNT ON in the database mail query:

  Create Procedure DailyCheckList

  As

  SET NOCOUNT ON

  Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)

  set @dt= convert(varchar(20),GETDATE(),107)

  Set @EmailSub='Daily Checklist for '+ @dt

  exec msdb..sp_send_dbmail

  @Profile_name = 'XXX',

  @recipients ='XXX',

  @subject =@emailSub ,

  @Body = @Msg , ------@ReturnMessage

  @query = 'SET NOCOUNT ON;select * from [DailyCheck_DiskSpace]; Select * from [dbo].[DailyCheckFailover]; select * from [dbo].[DailyCheckFailedJobs]' ,

  @body_format = 'HTML'

  -- Gianluca Sartori