Jens Ottersberg


Hi,

I use SQL Server 2005 and I've a problem with Database Mail. Suddenly it doesn't work anymore. Possible Causes:
Installation of Reporting Services
Installation of Service Packs 1 & 2

The Problem:
It seems that the tables ExternalMailQueue and InternalMailQueue (are that tables ) are gone away.

EXEC msdb.dbo.sysmail_start_sp says that ExternalMailQueue doesn't exist.

The Log says:

Datum 13.08.2007 08:21:21
Protokoll Datenbank-E-Mail (Datenbank-E-Mail-Protokoll)

Protokoll-ID 72
Prozess-ID 1016
Zuletzt geandert 13.08.2007 08:21:21
Zuletzt geandert von BI\lahrssen

Meldung
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Fehler beim Lesen der Datenbank. Ursache: Ungultiger Objektname 'ExternalMailQueue'.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
bei Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueItemFromCommand(SqlCommand c)
bei Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.QueueDataReader.GetQueueData(Int32 receiveTimeoutSec)
bei Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetDataFromQueue(Int32 receiveTimeoutSec)
bei Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)

2) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 16
LineNumber: 47
Number: 208
Procedure: sp_readrequest
Server: .
State: 12
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Ungultiger Objektname 'ExternalMailQueue'.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL

StackTrace Information
===================
bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
bei System.Data.SqlClient.SqlDataReader.get_MetaData()
bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
bei System.Data.SqlClient.SqlCommand.ExecuteReader()
bei Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ExecuteReader(SqlCommand command)



Is someone able to help me


Or is it possible to create the tables new Then I just need the script for creating them.



Re: Problem with Database Mail: ExternalMailQueue missing

Ryan Garaygay


I'm having a similar issue (on SP2). Any ideas





Re: Problem with Database Mail: ExternalMailQueue missing

Ryan Garaygay

The QUEUE mentioned should be present in msdb for Database Mail to work.

I didn't have any issues reinstalling SQL Server (development machine) so I did just that. Fortunately this wasn't an issue in the production database.

Installed the service packs after reinstalling and the queues are still there so that did the trick for me.

I wasn't able to look further into what could have caused this though and if simply recreating the queue (eg. thru script from another machine) would solve the issue.






Re: Problem with Database Mail: ExternalMailQueue missing

TopSQL

I see the same issue with no queue record in msdb.

Can you send the script from machine, I have same info in all 3 machines.

thanks a lot.






Re: Problem with Database Mail: ExternalMailQueue missing

Ryan.Garaygay

In case it would be of help to anyone, here's a script for recreating the said queues (generated from my SMSS)

USE [msdb]

GO

CREATE QUEUE [dbo].[ExternalMailQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[sp_sysmail_activate] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ) ON [PRIMARY]

GO

CREATE QUEUE [dbo].[InternalMailQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[sp_ExternalMailQueueListener] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' ) ON [PRIMARY]