Reg : Sql Database mails

• aby_dvd

  Ten Centuries

  Points: 1310

  More actions

  July 30, 2010 at 6:05 am

  #224773

  I configured database mail for the first time in SQL 2005 and i was successful .

  I recieved a mail but it just showed me that the job was successful but what i want now is to recieve the output of the query also along with whether the job was run successfully or not

  e.g. i have kept sp_who2 active in the scripts of a particular job .

  Now along with whether the job ran successfully or not , I want the output of the query too in the mail .

  Thanks in advance .

  Keep scripting 🙂

• Ramesh S-391202

  SSC Journeyman

  Points: 87

  More actions

  July 30, 2010 at 7:07 am

  #1200285

  Hi

  you can achieve this using below method

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'AdventureWorks2008R2 Administrator',

  @recipients = 'danw@Adventure-Works.com',

  @query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder

  WHERE DueDate > ''2006-04-30''

  AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,

  @subject = 'Work Order Count',

  @attach_query_result_as_file = 1 ;

  or u can use attachment mehtod.

  Step1: execute script and out as text file.

  step 2: success message with attachement

  Thanks
  S. Ramesh
  
  www.sequalserver.com