Thursday 15 December 2011

Database Mail stopped working after install SQL Server 2005 SP4 and CU 3

Hi everybody,

The server have the last Service Pack (SP4) and the last Cumulative Update (CU3): Product Version and Resource Version is 9.00.5292.00. [ SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('ResourceVersion'); ]

After hours looking for a solution without any result I decided to make it by myself....I find  a workarround and the solution...

The error:

Log  Database Mail (Database Mail Log)
Log ID  68124
Process ID  6552
Last Modified By  DOMAIN\user

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

Workarround:

You need another SQL Server 2005 with a running version on Database mail(You can use another existing SQL Server in your organization or install one in a Virtual Machine.)
  1. Configure this new server as a linked server (Remember to allow 'RPC out' in Linked Server properties and 'Server Options').
  2. Modify the Call to stored procedure, to call the linked server:
    1. Usually you call:          EXEC msdb.dbo.sp_send_dbmail...
    2. Now you must call:      EXEC linkedserver.msdb.dbo.sp_send_dbmail...
Solution:

After install all the possible updates to the SQL Server instance i pay more attention to the Microsoft KB: http://support.microsoft.com/kb/2008286 which applies to the same error in SP3. If you goes to the Cause section you can read:
...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.
Therefore I only need to...
  1. Overwrite the failing stored procedure (sysmail_help_admin_account_sp, msdb system stored procedures) by another of  a running databasemail instance.
    1. Then the problem is the table msdb.dbo.sysmail_server  don't have the colum of timeout value and you recive an error when update the stored procedure.
  2. No problem, you only need to add the column timeout(int, allow nulls) to the table msdb.dbo.sysmail_server.
  3. Rerun the update of storedprocedure and the databasemail is running again!!!!!

Hope to help you!!