CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

DBMail has Suddenly Stopped Working

DBMail has Suddenly Stopped Working

  • Comments 5

 

The other day we ran into a strange Database Mail issue here in SQL support. Customers were running into issues where DBMail would suddenly stop working after doing service pack upgrades to their SQL Servers.This was happening on both SQL 2005 and SQL 2008.

The error was also unusual in that the word "timeout" could make you believe we were dealing with a performance issue. Here is what you will see in the SQLAgent.OUT log file.

2009-11-03 22:57:37 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2009-11-03 22:57:38 - ! [260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.IndexOutOfRangeException: timeout
   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at System.Data.SqlClient.SqlDataReader.get_Item(String name)
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
   --- E)

Turns out that the "timeout" is a new field name in a msdb table and hence the DBMail binaries are looking for this field when it calls our stored procedure!

Additional Errors you may encounter trying to run DBMail commands manually:

2009-11-03 22:57:38 - ! [355] The mail system failed to initialize; check configuration settings

Exception Information

Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException

Message: Mail configuration information could not be read from the database.

Data: System.Collections.ListDictionaryInternal

TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)

HelpLink: NULL

Source: DatabaseMailEngine

 

StackTrace Information

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)

   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)

   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)

   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

 

Exception Information

Exception Type: System.IndexOutOfRangeException

Message: timeout

Data: System.Collections.ListDictionaryInternal

TargetSite: Int32 GetOrdinal(System.String)

HelpLink: NULL

Source: System.Data

 

StackTrace Information

   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)

   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)

   at System.Data.SqlClient.SqlDataReader.get_Item(String name)

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID

 

In every case "GetOrdinal" is part of the error messsage. After much research, here are the scenarios in which you will encounter this problem:

SQL 2008 Scenario

  1. Your current SQL 2008 instance is SQL 2008 RTM CU 6 or higher.
  2. You then upgrade that instance to SQL Server 2008 Service Pack 1 and no higher than Cumulative update 1 (CU1).

SQL 2005 Scenario:

  1. Your current SQL 2005 instance is SQL 2005 Service Pack 2 CU13 or higher.
  2. You then upgrade that instance to SQL Server 2005 Service Pack 3 and no higher than Cumulative update 3 (CU3). 

Starting with Cumulative Update Package 6 for SQL Server 2008 and Cumulative Update Package 13 for SQL Server 2005 Service pack 2, Database mail recieved new versions of the binary files and schema definitions. When applying service packs to these instances as outlined in the Symptoms section, the upgraded binaries are not touched but the stored procedures associated with Database Mail get overwritten by instmsdb.sql script in SQL Server 2008 or by sqldbupg.sql in SQL Server 2005. Theses scripts contain older stored procedure definitions that don't include the "timeout" column. When the DBMail binaries make a call to the system stored procedure sysmail_help_admin_account_sp, they expect a "timeout" column to be returned by the stored procedure. Though this column exists in the underlying schema\table, the newer version of the stored procedure does not return this column and hence you encounter IndexOutOfRangeException, on the GetOrdinal call.

To fix DBMail, all you need to do is upgrade the just installed Service Pack to the latest cumulative update for that SQL version. This will correct the schema definitions for the stored procs (DBMail dlls are the same and the underlying table remain the same).

Note: Use the resolution appropriate for your environment:

SQL Server 2008: Apply Cumulative Update Package 2 (or a higher version) for SQL Server 2008 Service Pack 1.

SQL 2008 SP1 CU2:970315 Cumulative update package 2 for SQL Server 2008 Service Pack 1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;970315

SQL Server 2005: Apply Cumulative Update Package 4 (or a higher version) for SQL Server 2005 Service Pack 3.

SQL 2005 SP3 CU4: 970279 Cumulative update package 4 for SQL Server 2005 Service Pack 3
http://support.microsoft.com/default.aspx?scid=kb;EN-US;970279

Look for a KB article in the next 2 or 3 weeks that will completely document this situation. I wanted to give you a "heads up" on this so that you can resolve it quickly and without too much frustration.

Eric Burgess
SQL Server Escalation Team

 

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Well,

    This problem appeared on one of my SQL 2005 servers AFTER the SQL 2005 CU6 upgrading.

    So, I reckon, scenarios are not complete.

  • I can't even get Word Art to work properly in Microsoft to work properly.

    unable to install from 2nd CD Disk  C:/Program Files/ Microsoft SQL Server /90/ Setup Bootstrap /LOG/ Summary.txt

  • Well, i have this issue on a SQL 2005 SQL instance at the following patch level: 2005 SP3+Q974648 (Cumulative HF6).  So the the scenarios are definitely not complete.  what is the work around now??????

  • Bump...  This same exact issue suddenly began to occur about a week ago for me.  DB mail suddenly quit working with this same error.  I'm currently on SP4 (9.00.5000.00)

    Any explination for that one?

  • The identical situation is occuring on SQL Server 2005 with SP4 at both the 9.0.5259 and 9.0.5292 levels

    It is wasting a lot of time.

Page 1 of 1 (5 items)