FIX: Sending Maintenance Report by E-mail to Operator Fails in Maintenance Plan (279867)

MORE INFORMATION

To work around this problem, you can include the script below as an additional job step in the last job created by a particular Maintenance Plan. This script below sends the last report file for a specific Maintenance Plan to a specified e-mail address.

To use this workaround, follow these steps:

1. Identify the last job for the Maintenance Plan.
2. Right-click the job, click Properties, click Steps, select the step, and then click Edit.
3. On the Edit Job Step dialog box, click the Advanced tab.
4. Set On Success Action to Go To Next Step.
5. Click OK on the Edit Job Step dialog box.
6. Click New to add a new step, and then give the step a name. Type should be Transact-SQL Script (TSQL) and Database should be master.
7. Paste the following script in the command window:

   declare @planname varchar(100)
   declare @dir varchar(200)
   declare @operator varchar(50)
   declare @cmd varchar (200)
   declare @mailfilename varchar(200)
   declare @filenamelen int
   
   --Values set here can actually be provided as parameters to a stored procedure.
   --If provided as parameters to a stored procedure, rem the following select statements.
   --@plananme is the plan whose maintenance report is sent.
   --@dir is the log directory for SQL Server. It is the directory to which the 
   --maintenance report files are written. 
   --@operator is the email address of the person to whom the report file should be mailed.
   
   select @planname = 'Database Maintenance Plan 1'
   select @dir ='c:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\LOG'
   select @operator ='email@domain.com'
   --You can automatically set the above by reading various values from SQL Server.
   
   SET NOCOUNT ON
   IF RIGHT (@dir, 1) <> '\' 
   begin
   select @dir =@dir +'\'
   end
   SELECT @dir = 'dir /s /b '+'"'+@dir + @planname+'*.txt'+'"' +' >c:\dir.txt'
   
   create table #TMP_MAINT_FILENAMES (NAME1 varchar(8000))
   exec xp_cmdshell @dir
   BULK INSERT #TMP_MAINT_FILENAMES
      FROM 'c:\dir.txt'
      WITH 
         (
            ROWTERMINATOR = '\n'
         )
   
   select @mailfilename=MAX(name1) from #TMP_MAINT_FILENAMES
   print 'The following file is being sent as an atachement'
   print @mailfilename
   
   --Set the various parameters for xp_sendmail.
   declare @tmpmessage varchar(300)
   declare @tmpsubject varchar(300)
   select @tmpmessage = 'This is the last maintenance report on the server for the maintenance plan '+@planname
   select @tmpsubject = 'SQL Server Maintenance Report for '+@planname
   
   --Send the last file for the maintenance plan.
   exec master..xp_sendmail @recipients= @operator, @subject =@tmpsubject, @message=@tmpmessage, 
   @attachments= @mailfilename
   
   --Perform cleanup here.
   drop table #TMP_MAINT_FILENAMES
   exec master..xp_cmdshell "del c:\dir.txt"
   SET NOCOUNT OFF
   					

8. Make sure that the @planname, @dir, and @operator values are set in the script.
9. Save the job step.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.