FIX: The xp_sendmail extended stored procedure does not run successfully in SQL Server 2000 SP2 or later (833045)


The information in this article applies to:

  • Microsoft SQL Server 2000 Desktop Engine (MSDE) SP2
  • Microsoft SQL Server 2000 Desktop Engine (MSDE) SP3
  • Microsoft SQL Server 2000 Enterprise Edition SP2
  • Microsoft SQL Server 2000 Enterprise Edition SP3
  • Microsoft SQL Server 2000 Enterprise Evaluation Edition SP2
  • Microsoft SQL Server 2000 Enterprise Evaluation Edition SP3
  • Microsoft SQL Server 2000 Personal Edition SP2
  • Microsoft SQL Server 2000 Personal Edition SP3
  • Microsoft SQL Server 2000 Standard Edition SP2
  • Microsoft SQL Server 2000 Standard Edition SP3
Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SQL Server 8.0:362765

SYMPTOMS

When you try to send an e-mail message by using the xp_sendmail extended stored procedure on a computer that is running Microsoft SQL Server 2000 Service Pack 2 (SP2) or later, the xp_sendmail extended stored procedure may not run successfully. You may receive the following error message:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040115
Note This problem typically occurs when the following two conditions are true:
  • There is a loss of network connectivity between the computer that is running Microsoft SQL Server 2000 and the computer that run Microsoft Exchange Server.
  • The Exchange server is restarted after the first execution of the xp_sendmail extended stored procedure on instance of SQL Server where the error occurred.

RESOLUTION

Service pack information

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

290211 How to obtain the latest SQL Server 2000 service pack

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
   Date         Time   Version             Size   File name
   ---------------------------------------------------------------------
   31-May-2003  18:45  2000.80.818.0      78,400  Console.exe      
   28-Oct-2003  04:21  2000.80.873.0     315,968  Custtask.dll     
   30-Jan-2004  16:29  2000.80.911.0      33,340  Dbmslpcn.dll     
   25-Apr-2003  02:12                    786,432  Distmdl.ldf
   25-Apr-2003  02:12                  2,359,296  Distmdl.mdf
   30-Jan-2003  01:55                        180  Drop_repl_hotfix.sql
   12-Sep-2003  03:26  2000.80.859.0   1,905,216  Dtspkg.dll       
   26-Aug-2003  20:16  2000.80.854.0     528,960  Dtspump.dll      
   23-Jun-2003  22:40  2000.80.837.0   1,557,052  Dtsui.dll        
   23-Jun-2003  22:40  2000.80.837.0     639,552  Dtswiz.dll       
   24-Apr-2003  02:51                    747,927  Instdist.sql
   03-May-2003  01:56                      1,581  Inst_repl_hotfix.sql
   30-Jan-2004  16:29  2000.80.911.0      90,692  Msgprox.dll      
   01-Apr-2003  02:07                      1,873  Odsole.sql
   30-Jan-2004  16:29  2000.80.911.0      62,024  Odsole70.dll     
   30-Jan-2004  16:29  2000.80.911.0      25,144  Opends60.dll     
   07-May-2003  18:47                    132,096  Opends60.pdb
   30-Jan-2004  16:28  2000.80.911.0      57,904  Osql.exe         
   02-Apr-2003  23:15  2000.80.797.0     279,104  Pfutil80.dll     
   04-Aug-2003  18:17                    550,780  Procsyst.sql
   12-Sep-2003  00:37                     12,305  Qfe469315.sql
   22-May-2003  22:57                     19,195  Qfe469571.sql
   30-Jan-2004  01:16                  1,090,380  Replmerg.sql
   30-Jan-2004  16:29  2000.80.911.0     221,768  Replprov.dll     
   30-Jan-2004  16:29  2000.80.911.0     307,784  Replrec.dll      
   29-Jan-2004  23:24  2000.80.911.0     159,813  Replres.rll
   06-Sep-2003  00:00                  1,087,150  Replsys.sql
   13-Aug-2003  16:28                    986,603  Repltran.sql
   30-Jan-2004  16:29  2000.80.911.0     287,304  Rinitcom.dll     
   30-Jan-2004  16:29  2000.80.911.0      57,916  Semnt.dll        
   29-Jul-2003  20:13  2000.80.819.0     492,096  Semobj.dll       
   31-May-2003  18:27  2000.80.818.0     172,032  Semobj.rll
   02-Jan-2004  19:42  2000.80.904.0      53,832  Snapshot.exe     
   09-Dec-2003  20:07                    117,834  Sp3_serv_uni.sql
   05-Feb-2004  00:46  2000.80.913.0      28,672  Sqlagent.dll     
   05-Feb-2004  00:47  2000.80.913.0     311,872  Sqlagent.exe     
   19-Feb-2004  18:02  2000.80.916.0     168,001  Sqlakw32.dll     
   30-Jan-2004  16:29  2000.80.911.0   4,215,360  Sqldmo.dll       
   07-Apr-2003  17:44                     25,172  Sqldumper.exe    
   29-Jan-2004  23:17  2000.80.911.0      28,672  Sqlevn70.rll
   05-Mar-2004  17:13  2000.80.922.0     180,792  Sqlmap70.dll     
   03-Sep-2003  02:56  2000.80.857.0     188,992  Sqlmmc.dll       
   02-Sep-2003  23:03  2000.80.857.0     479,232  Sqlmmc.rll
   22-Oct-2003  00:08  2000.80.871.0     401,984  Sqlqry.dll       
   30-Jan-2004  16:29  2000.80.911.0      57,920  Sqlrepss.dll     
   09-Mar-2004  19:54  2000.80.922.0   7,614,545  Sqlservr.exe     
   09-Mar-2004  19:57                 12,788,736  Sqlservr.pdb
   30-Jan-2004  16:29  2000.80.911.0     590,396  Sqlsort.dll      
   30-Jan-2004  16:29  2000.80.911.0      45,644  Sqlvdi.dll       
   30-Jan-2004  16:29  2000.80.911.0     106,588  Sqsrvres.dll     
   30-Jan-2004  16:29  2000.80.911.0      33,340  Ssmslpcn.dll     
   30-Jan-2004  16:29  2000.80.911.0      82,492  Ssnetlib.dll     
   30-Jan-2004  16:29  2000.80.911.0      25,148  Ssnmpn70.dll     
   28-Oct-2003  04:21  2000.80.873.0     123,456  Stardds.dll      
   30-Jan-2004  16:29  2000.80.911.0     158,240  Svrnetcn.dll     
   30-Jan-2004  16:29  2000.80.911.0      76,416  Svrnetcn.exe     
   30-Apr-2003  23:52  2000.80.816.0      45,132  Ums.dll          
   30-Apr-2003  23:52                    132,096  Ums.pdb
   30-Jan-2004  16:29  2000.80.911.0      98,872  Xpweb70.dll
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

WORKAROUND

To work around this problem, verify the network connectivity, and then resend the mail. You can set up the retry option for the xp_sendmail extended stored procedure by configuring the registry entries or by adding retry logic to your application code. To do this, use one of the following methods:
  • Method 1: Configure the retry option for the xp_sendmail extended stored procedure

    By default, after you install this fix, if the xp_sendmail extended stored procedure encounters HRESULT = 0x80040115, the xp_sendmail extended stored procedure tries to send the mail again after it sleeps for 1000 milliseconds. However, you can configure the number of retries and the wait interval for each retry.

    Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
    1. To configure the number of retries, add the NumberOfTimesToRetryOnNetworkError DWORD value to the following registry key, and then set the value:
      • For a default instance of SQL Server 2000, use the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer registry key.
      • For a named instance of SQL Server 2000, use the HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer registry key.
    2. To configure the wait interval for the xp_sendmail retry option, add the WaitIntervalForRetryOnNetworkError DWORD value to the following registry key, and then set the value:
      • For a default instance of SQL Server 2000, use the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer registry key.
      • For a named instance of SQL Server 2000, use the HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer registry key.
  • Method 2: Add retry logic to your application code

    To do so, follow these steps:
    1. Verify the network connectivity for the computer that is configured as the mail server.
    2. Make sure that the network adapter and the corresponding driver are working correctly.
    3. Use an appropriate logic in the Transact-SQL script that you use to send e-mail messages by using the xp_sendmail extended stored procedure so that the e-mail message can be resent after the failure occurs. To do so, you can use a Transact-SQL script that is similar to the following:
      DECLARE
@EmailAddTO varchar(30),
@EmailSubject varchar(130),
@EmailText varchar(255),
@return int,
@Counting int 

BEGIN
/* SET value */
SET @return = 0 
SET @Counting = 0 
SET @EmailSubject = 'TEST EMAIL' 
SET @EmailText = 'This is a test email'
SET @EmailAddTO = '<RecipientAddress>' 

/* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and then RETRY. */
WHILE 1=1
BEGIN
SET @Counting = @Counting + 1

EXEC @return = master.dbo.xp_sendmail 
@recipients = @EmailAddTO,
@message = @EmailText ,
@subject = @EmailSubject

/* CHECK value, break if SUCCESS */
IF @return = 0
BEGIN
PRINT 'EMAIL SENT'
BREAK
END
ELSE
BEGIN
/* Try 5 times */
IF @Counting = 5
BREAK 
PRINT 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
/* 000 hours, 00 minutes, and 10 seconds */
WAITFOR DELAY '000:00:10'
END
END
END
GO

MORE INFORMATION

When you send an e-mail message by using the xp_sendmail extended stored procedure, the xp_sendmail extended stored procedure makes many calls to the MAPI API. However, the retry option in this hotfix is only added for a single call that caused the failure with the following error message:
HRESULT = 0x80040115

REFERENCES

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

263556 How to configure SQL Mail

315886 Common SQL Mail problems

820220 0x80070005 or 0x80040005 error occurs when you send e-mail by using the xp_sendmail extended stored procedure

Modification Type:MajorLast Reviewed:1/19/2006
Keywords:kbHotfixServer kbQFE kbRegistry kbSQLServ2000preSP4fix kbfix kbbug kbemail kbDLL kbprb kberrmsg kbSQLServMail KB833045 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.