Prev: Build a date value
Next: What are the advantages of a query using a derived table(s) overa query not using them?
From: LN on 4 May 2010 18:40
I have try stop and start the db mail by executing the SPs below but no luck.
Does anyone has seem the error below and how to fix it.

Thanks in advance for your advise/help.

--steps
USE msdb;
go
EXECute sysmail_stop_sp;
go
EXECute sysmail_start_sp;
go

EXECute sysmail_help_queue_sp @queue_type = 'Mail';
go

queue_type length state
last_empty_rowset_time last_activated_time
---------- -----------
----------------------------------------------------------------
----------------------- -----------------------
mail 3 [red]INACTIVE[/red]
2010-05-04 22:26:16.850 2010-05-04 22:31:46.840

Error message:

ate 5/4/2010 3:26:21 PM
Log Database Mail (Database Mail Log)

Log ID 2346
Process ID 1032
Last Modified 5/4/2010 3:26:21 PM

Message
1) Exception Information
===================
Exception Type:
Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ValidateConnectionAndTransaction()
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
at
Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
at
Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
at
Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
at
Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String
dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)
From: John Bell on 5 May 2010 04:15
On Tue, 4 May 2010 15:40:01 -0700, LN <LN(a)discussions.microsoft.com>
wrote:

>I have try stop and start the db mail by executing the SPs below but no luck.
> Does anyone has seem the error below and how to fix it.
>
>Thanks in advance for your advise/help.
>
>--steps
>USE msdb;
>go
>EXECute sysmail_stop_sp;
>go
>EXECute sysmail_start_sp;
>go
>
>EXECute sysmail_help_queue_sp @queue_type = 'Mail';
>go
>
>queue_type length state
> last_empty_rowset_time last_activated_time
>---------- -----------
>----------------------------------------------------------------
>----------------------- -----------------------
>mail 3 [red]INACTIVE[/red]
> 2010-05-04 22:26:16.850 2010-05-04 22:31:46.840
>
>Error message:
>
>ate 5/4/2010 3:26:21 PM
>Log Database Mail (Database Mail Log)
>
>Log ID 2346
>Process ID 1032
>Last Modified 5/4/2010 3:26:21 PM
>
>Message
>1) Exception Information
>===================
>Exception Type:
>Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
>Message: The Transaction not longer valid.
>Data: System.Collections.ListDictionaryInternal
>TargetSite: Void ValidateConnectionAndTransaction()
>HelpLink: NULL
>Source: DatabaseMailEngine
>
>StackTrace Information
>===================
> at
>Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
> at
>Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
> at
>Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
> at
>Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String
>dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)


You don't check the return status of he first two procedures to see if
they worked.

What does sysmail_help_status_sp return? Can you run
sysmail_help_queue_sp on it's own if it is started?

John
 | 
Pages: 1
Prev: Build a date value
Next: What are the advantages of a query using a derived table(s) overa query not using them?