Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PracticalSQLDba

I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.

SQL server 2005 Database Mail stopped functioning after installing Service Pack 3


Recently I have upgraded one of our SQL server 2005 instance to Service Pack 3 and later point of time we have noticed that the database mail in that instance stopped working with the following error in Database Mail error log.

[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


Unfortunately in our QA environment , database mail is not used extensively and did not noticed this issue. After updating the service pack in the live , all our monitoring and alert mail stopped working.

The KB article 2008286 is clearly stated that the solution for this problem is to update the Cumulative Update 4 (CU4)  but for that  I need to go through the change management process and it is time consuming.

After spending some time, I have found a work around for this problem by updating sysmail_help_admin_account_sp stored procedure in msdb database. 

The reason behind this issues is , when the modified database mail binaries ( modified as part of  service pack 3)  makes call to the above mentioned stored  procedure , it expects a timeout column in the result set of the stored procedure,but the current version of stored procedure is not returning the timeout column and hence encounter IndexOutOfRangeException.The modified version of the stored procedure is available here to download. This stored procedure will be replaced by the Microsoft version while updating the CU4 for Service Pack 3 or later version 

If you liked this post, do like my page on FaceBook at http://www.FaceBook.com/PracticalSqlDba



Comments

Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...