Today we patched and upgraded one of our production SQL Server from SQL Server 2005 SP2 to SQL Server 2005 SP4. After upgrade our Database Mail that was configured to send mails from SQL Server stopped working and in the log below error was coming:

1) 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)

2) 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)

I deleted the Mail profile and Mail account and recreated them but still I kept on getting same error. I reinitialized Service Broker, checked the configuration in Surface Area and everything looked fine but our mail notification was not working…

After a lot of research, I found that we can rectify the issue by running the script named “sysdbupg.sql” from “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\install” ( location might be different if SQL installed in any other location)

This script basically upgrades all the SPs on System Databases.

Here are the sequence of steps I took before executing the script:

1) Took Full database backups
2) Ran the above script- it basically upgrades the SPs of System databases
3) Test SQL mail- and bingo!! It worked.
4) Again took System databases backups..

Hope this will help you someday!!

Subhro Saha