Email notification with attachments?

• Darryl864

  SSC Veteran

  Points: 250

  More actions

  April 28, 2008 at 3:04 am

  #184919

  I have created some SQL to email me messages when certain tasks fail in a maintenance plan. I would now like to attach the error log to the email to save me having to logon to the SQL Server. However the SQL fails as the file I am attempting to attach is in use (by SQL Server itself).

  USE msdb

  declare @myBody varchar(1000)

  Select @myBody= 'The main body of the message and date.'+Char(10)+Char(13)+ convert(varchar, getdate(), 6)+' - '+ convert(varchar, getdate(), 8);

  begin

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'myEmailAddress@bath.co.uk,

  @subject='EmailSubject',

  @body= @myBody,

  @profile_name ='SQL Server mail Profile';

  @file_attachments ='C:\FileAttachment.txt';

  end

  Would there be another way of doing this which would not fail if the file is in use? Or is there a way of making a copy of the file and emailing that?

  Note for security reasons XP_cmdshell has been disabled so I wont be able to use that either!

  Thanks in advance,

  Darryl

• Carolyn Richardson

  SSCrazy Eights

  Points: 8357

  More actions

  April 28, 2008 at 4:56 am

  #807675

  You can get the data from the error log with xp_readerrorlog, and extract as many rows as you need to send, as you may only want a day or so of logs.

  CREATE TABLE #TempLog

  (LogDate datetime, ProcessInfo varchar(50),ErrorLog VARCHAR (1000))

  INSERT INTO #TempLog

  EXEC('xp_readerrorlog')

  Facts are stubborn things, but statistics are more pliable - Mark Twain
  Carolyn
  SQLServerSpecialists[/url]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736479

  More actions

  April 28, 2008 at 8:57 am

  #807854

  Instead of sending the email, you could log the failure in a table, then have another job that comes around every 5 minutes or so, checks the table, then sends out the email for failed jobs. The file might be unlocked by then.