Error message when you use Database Mail after you apply a SQL Server service pack or cumulative update: "System.IndexOutOfRangeException: timeout"


Symptoms


Scenario 1: Consider the following sequence of events for Microsoft SQL Server 2008

  • You are running the release version of SQL Server 2008 that was updated with one of the following cumulative updates for the release version:
    • SQL Server 2008 Cumulative Update 6
    • SQL Server 2008 Cumulative Update 7
    • SQL Server 2008 Cumulative Update 8
  • You upgrade this instance to one of the following versions:

Scenario 2: Consider the following sequence of events for SQL Server 2005.

  • You are running an instance of SQL Server 2005 that was patched with SQL Server 2005 SP2 Cumulative Update 13 or a later version.
  •  You upgrade this instance to one of the following versions:
    • SQL Server 2005 Service Pack 3
    • SQL Server 2005 SP3 Cumulative Update 1
    • SQL Server 2005 SP3 Cumulative Update 2
    • SQL Server 2005 SP3 Cumulative Update 3

In either of these scenarios, Database Mail stops working, and messages that resemble the following are logged in the SQL Agent log. 

Error Message 1

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)

Error Message 2

 

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

Cause


Starting with Cumulative Update Package 6 for SQL Server 2008 and with Cumulative Update Package 13 for SQL Server 2005 Service Pack 2, Database Mail received a new version of the binary files and schema definitions. When you apply service packs to these instances as outlined in the "Symptoms" section, the upgraded binaries are not touched. However, the stored procedures that are associated with Database Mail are overwritten by the instmsdb.sql script in SQL Server 2008 and by the sysdbupg.sql script in SQL Server 2005. When the 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. Although this column exists in the underlying schema, the newer version of the stored procedure does not return this column. Therefore, you encounter IndexOutOfRangeException on the GetOrdinal call.

Resolution


Note Use the resolution that is appropriate for your environment:

  • SQL Server 2008: Apply Cumulative Update Package 2 for SQL Server 2008 Service Pack 1 or a later version.
  • SQL Server 2005: Apply Cumulative Update Package 4 for SQL Server 2005 Service Pack 3 or a later version.

More Information


In environments where the SQL instances that are affected by this issue cannot be upgraded to the fixed versions and where Database Mail has to be running immediately, use the following Transact-SQL script to re-create the sysmail_help_admin_account_sp stored procedure with the timeout column included.

Important If for any reason these scripts do not resolve the issue, you must upgrade to the appropriate Cumulative Update that is listed in the "Resolution" section. Make sure that you set customer expectations accordingly.