Partager via

Erreur « La transaction n’est plus valide » lorsque la messagerie de base de données ne parvient pas à envoyer un message dans SQL Server

Cet article vous aide à résoudre le problème où la messagerie de base de données ne parvient pas à envoyer un message.

Version de produit d’origine : SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 sur Linux, SQL Server 2017 sur Windows
Numéro de base de connaissances d’origine : 4502457

Symptômes

Supposons qu’un utilisateur qui exécute Microsoft SQL Server ne peut pas envoyer de messagerie de base de données. Dans ce cas, le journal de messagerie de base de données (sysmail_event_log) affiche l’entrée suivante :

Informations sur l’exception : Type d’exception : Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message : La transaction n’est plus valide.
Données : System.Collections.ListDictionaryInternal
TargetSite : Void ValidateConnectionAndTransaction()
HelpLink : NULL
Source : DatabaseMailEngine
Informations StackTrace : sur Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
sur Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
sur 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, Byte[] encryptionKey, Int32 connectionTimeout)',@proc

Note

  • L’expression non valide apparaît de cette façon dans le champ Message pour signifier que la transaction n’est plus valide.
  • Vous pouvez voir le même message dans le journal des applications. Le message électronique reste dans l’état « nouvelle tentative » et sysmail_unsentitemsreste indétenu jusqu’à ce que le programme externe DatabaseMail.exe puisse s’exécuter correctement.

Cause

L’option de connexion par défaut SQL Server utilise SET NUMERIC_ARITHABORT ON. Lorsque vous exécutez sp_send_dbmail, le message électronique est mis en file d’attente vers ExternalMailQueue. Lorsqu’un message apparaît dans la file d’attente, la procédure stockée d’activation déclenche le DatabaseMail.exe exécutable externe. Lorsque DatabaseMail.exe est connecté à SQL Server, il s’exécute sp_readrequest pour lire les messages à partir de la file d’attente. Pendant l’exécution de sp_readrequest, vous remarquerez peut-être que l’exception se produit. L’instruction suivante SELECT est exécutée ( sp_readrequest vous devez collecter le suivi au niveau de l’instruction pour voir cette SELECT instruction) :

DatabaseMail - DatabaseMail - Id\<ProcessId>        \<NTUserName>        \<SPID>                \<StartTime>              msdb        \<LoginSid>  \<SessionLoginName>
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort on
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
2 - Pooled        1        1 - Non-DAC
RPC:Starting <BinaryData> 4 <NTUserName> DatabaseMail - DatabaseMail - Id<ProcessId> <NTUserName> <SPID> <StartTime> sp_readrequest msdb <LoginSid> <SessionLoginName> exec sp_readrequest @receive_timeout=600000

SP:StmtStarting <BinaryData> 4 <NTUserName> DatabaseMail - DatabaseMail - Id<ProcessId> <NTUserName> <SPID> <StartTime> sp_readrequest msdb <LoginSid> <SessionLoginName>

SELECT @mailitem_id = MailRequest.Properties.value('(MailItemId)[1]', 'int')  
FROM @xmlblob.nodes('
declare namespace requests="https://schemas.microsoft.com/databasemail/requests]";/requests:SendMail')

AS MailRequest(Properties)
If SET NUMERIC_ARITHABORT ON is set as default connection option, this SELECT statement will encounter error 1934 and an exception will occur:

Exception 4 <servername> DatabaseMail - DatabaseMail - Id<ProcessId> <NTUserName> <SPID> <StartTime> 1934 msdb <LoginSid> <SessionLoginName> SELECT failed because the following SET options have incorrect settings:  
'NUMERIC_ROUNDABORT'.  
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatialindex operations.

Lorsque DatabaseMail.exe rencontre l’exception, une restauration est tentée mais échoue. L’exception provoque l’expiration de la transaction. Pour cette raison, une transaction non valide n’est plus enregistrée dans le journal de messagerie de base de données.

Toutefois, la cause racine du problème est que l’erreur 1934 se produit en raison d’une option SET incompatible lorsque la méthode de type de données XML (MailRequest.Properties.value('(MailItemId)[1]', 'int')) est utilisée dans l’instructionSELECT.

Vérifier le message d’erreur

  • Vérifiez si le message d’erreur dans le journal de messagerie de base de données est le même message (la transaction n’est plus valide).

  • Rassemblez une trace de profileur en activant les événements, erreurs et avertissements au niveau de l’instruction et les événements Broker activés.

  • Vérifiez le paramètre d’instance SQL Server pour connaître les options de connexion par défaut. Pour ce faire, ouvrez SQL Server Management Studio, cliquez avec le bouton droit sur Serveur, puis sélectionnez Options de connexion par défaut des>connexions>>par défaut d’abandon d’arrondi.

Résolution

Pour résoudre ce problème, remplacez l’option de connexion par défaut par SET NUMERIC_ROUNDABORT OFF.