Microsoft KB Archive/279867

Knowledge Base

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

Article ID: 279867

Article Last Modified on 6/23/2005

---

APPLIES TO

• Microsoft SQL Server 2000 Standard Edition

---

This article was previously published under Q279867

BUG #: 351140 (SHILOH_BUGS)

SYMPTOMS

A SQL Server Database Maintenance Plan includes an option to send a maintenance report (that is, a file that contains results for the execution of the maintenance plan) by e-mail to a predefined operator on the server. E-mailing the maintenance report file to the operator may fail with the following error message, which can be found in the maintenance report for the Database Maintenance Plan:

Error 18025: [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80004005

This error does not affect the reporting of the job status; the job itself is shown as successful.

CAUSE

The xp_sendmail extended stored procedure fails with the above error when attempting to send an open file as an e-mail attachment. Sqlmaint.exe executes the Maintenance Plan and writes output to the report file. The final step in the Maintenance Plan, which is to send an e-mail, is also recorded in the report. Because the report file is still open when xp_sendmail tries to send it as an attachment, the attempt fails.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

For a workaround to this problem, see the "More Information" section of this article.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

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.

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB279867

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.