DataBase Mail: Problem with 2005 SP3 on named instances

Question

DataBase Mail: Problem with 2005 SP3 on named instances

ckrempel

SSC Enthusiast

Points: 118
More actions
December 29, 2014 at 9:02 am

#313315

I have a machine with a default 2005 SP3 instance and 2 named instances at same level.
All are SQL Server 9.0.4294 (Cumulative Update Package 9 for SQL Server 2005 Service Pack 3).
The default instance not having issues sending mail, but the 2 named instances are. Message (see below) indicates a problem getting at the TIMEOUT column because 2005 Service Pack 2 received a new version of the binary files and schema definitions.
I have experimented with the DatabaseMailUserRole in msdb, and also attempted to create different profiles and users ... none have solved the issue.
Any suggestions appreciated. Thanks.
Specific error text:
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)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

lloks like you need yet another cumulative update?

https://support.microsoft.com/kb/2008286?wa=wsignin1.0
Error message when you use Database Mail after you apply a SQL Server service pack or cumulative update: "System.IndexOutOfRangeException: timeout"
Print
Email
Article translations
Article ID: 2008286 - View products that this article applies to.
Expand all | Collapse all
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:
•SQL Server 2008 Service Pack 1
•SQL Server 2008 Service Pack 1+ Cumulative Update for Service Pack 1
•Slipstreamed version of SQL Server 2008 Service Pack 1 and Cumulative Update for Service Pack 1
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
Back to the top | Give Feedback
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.
Back to the top | Give Feedback
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.Back to the top | Give Feedback
Properties
Article ID: 2008286 - Last Review: October 23, 2012 - Revision: 3.0
Applies to
•Microsoft SQL Server 2005 Service Pack 3
•Microsoft SQL Server 2008 Service Pack 1
Keywords:
KB2008286

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ckrempel SSC Enthusiast Points: 118 More actions · Answer 2

Thank you ... I'll see when I can schedule that ...

But why would 1 of the 3 instances be working?

That's what really puzzles me.