Pages

Monday, 26 March 2012

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



18 comments:

  1. Thanks man, you fix worked. Microsoft is bloody useless. KB article says: install SP3 package 4 and the server is already SP4! Cheers.

    ReplyDelete
  2. Just a note for any other MS victims out there: I didn't paste the whole procedure, but just the @timeout bits as mine didn't have the commented out part and God knows whatever else.

    ReplyDelete
    Replies
    1. Good to hear that resolved your issue...

      Delete
  3. May God bless you. Tq.

    ReplyDelete
  4. Great, I search this solution above two weeks. Thanks very much

    ReplyDelete
  5. Hey buddy, you saved me lots of time and headaches, too!
    Thank you so much!!

    ReplyDelete
  6. This was helpful thank you.

    ReplyDelete
  7. Greatly helpful thanks

    ReplyDelete
  8. Really solved my problem. thanks a lot

    ReplyDelete



  9. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

    ReplyDelete
  10. Direct mail marketing means publicising about the company offerings containing personalized promotional message by using the mailing route. This direct mail letter can be in any format like catalogue, leaflets, coupons, postcards, CDs and DVDs etc.
    extract emails

    ReplyDelete
  11. Oracle fusion financials online training institute we have our branch over all the India.
    Oracle Fusion Cloud HCM Online Training in Hyderabad, Bangalore, Delhi, Chennai, Kolkata , Pune,
    Mumbai, Ahmedabad, Gurgon, Noida, India, Dubai, UAE, USA, Kuwait, UK, Singapore, Saudi Arabia,
    Canada, Oracle Fusion HCM Online Trainings
    thank for sharing information
    oracle fusion financials training
    oracle fusion financials online training
    oracle fusion Procurement training training
    oracle fusion procurement online training


    ReplyDelete
  12. when u have time plz write this type of articles useful info
    Oracle Fusion Financials Online Training

    ReplyDelete