FIX: Sending Maintenance Report by E-mail to Operator Fails in Maintenance Plan
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 351140 (SHILOH_BUGS)
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 0x80004005This error does not affect the reporting of the job status; the job itself is shown as successful.
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.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
For a workaround to this problem, see the "More Information" section of this article.
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
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:
- Identify the last job for the Maintenance Plan.
- Right-click the job, click Properties, click Steps, select the step, and then click Edit.
- On the Edit Job Step dialog box, click the Advanced tab.
- Set On Success Action to Go To Next Step.
- Click OK on the Edit Job Step dialog box.
- 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.
- 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@example.com'--You can automatically set the above by reading various values from SQL Server.SET NOCOUNT ONIF RIGHT (@dir, 1) <> '\' beginselect @dir =@dir +'\'endSELECT @dir = 'dir /s /b '+'"'+@dir + @planname+'*.txt'+'"' +' >c:\dir.txt'create table #TMP_MAINT_FILENAMES (NAME1 varchar(8000))exec xp_cmdshell @dirBULK INSERT #TMP_MAINT_FILENAMES FROM 'c:\dir.txt' WITH ( ROWTERMINATOR = '\n' )select @mailfilename=MAX(name1) from #TMP_MAINT_FILENAMESprint '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 '+@plannameselect @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_FILENAMESexec master..xp_cmdshell "del c:\dir.txt"SET NOCOUNT OFF
- Make sure that the @planname, @dir, and @operator values are set in the script.
- Save the job step.
Article ID: 279867 - Last Review: 01/16/2015 21:08:43 - Revision: 6.0
Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB279867