sp_send_dbmail (Transact-SQL)

Envoie un message électronique aux destinataires spécifiés. Le message peut comprendre un ensemble de résultats de requête et des fichiers joints. Lorsque le courrier est correctement intégré dans la file d'attente de la messagerie de base de données, la procédure sp_send_dbmail retourne l'élément mailitem_id du message. Cette procédure stockée se trouve dans la base de données msdb.

S'applique à : SQL Server (SQL Server 2008 via la version actuelle).

Conventions de la syntaxe Transact-SQL

Syntaxe

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @from_address = ] 'from_address' ]
    [ , [ @reply_to = ] 'reply_to' ] 
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ] 
…………[ , [@query_result_no_padding = ] @query_result_no_padding ] 
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Arguments

• [ @profile_name= ] 'profile_name'
  Nom du profil à partir duquel envoyer le message. L'argument profile_name est de type sysname, avec NULL comme valeur par défaut. Il doit être le nom d'un profil de messagerie de base de données existant. Lorsqu'aucun argument profile_name n'est spécifié, la procédure stockée sp_send_dbmail utilise le profil privé par défaut de l'utilisateur actuel. Si cet utilisateur ne dispose pas d'un tel profil, sp_send_dbmail utilise le profil public par défaut de la base de données msdb. Si l'utilisateur n'a pas de profil privé par défaut et s'il n'existe aucun profil public par défaut pour la base de données, le paramètre @profile_name doit être spécifié.

• [ @recipients= ] 'recipients'
  Liste des adresses de messagerie électronique auxquelles envoyer le message (délimitée par des points-virgules). La liste des destinataires est de type varchar(max). Même si ce paramètre est facultatif, au moins l'une des options @recipients, @copy_recipients ou @blind_copy_recipients doit être spécifiée ; dans le cas contraire, sp_send_dbmail retourne une erreur.

• [ @copy_recipients= ] 'copy_recipients'
  Liste des adresses de messagerie électronique auxquelles envoyer une copie du message (délimitée par des points-virgules). La liste des destinataires en copie est de type varchar(max). Même si ce paramètre est facultatif, au moins l'une des options @recipients, @copy_recipients ou @blind_copy_recipients doit être spécifiée ; dans le cas contraire, sp_send_dbmail retourne une erreur.

• [ @blind_copy_recipients= ] 'blind_copy_recipients'
  Liste des adresses de messagerie électronique auxquelles envoyer une copie invisible du message (délimitée par des points-virgules). La liste des destinataires en copie invisible est de type varchar(max). Même si ce paramètre est facultatif, au moins l'une des options @recipients, @copy_recipients ou @blind_copy_recipients doit être spécifiée ; dans le cas contraire, sp_send_dbmail retourne une erreur.

• [ @from_address= ] 'from_address'
  Représente la valeur de l'adresse de l'expéditeur du message électronique. Il s'agit d'un paramètre optionnel utilisé pour remplacer les paramètres dans le profil de messagerie. Ce paramètre est de type varchar(MAX). Les paramètres de sécurité SMTP déterminent si ces substitutions sont acceptées. Si aucun paramètre n'est spécifié, la valeur par défaut est NULL.

• [ @reply_to= ] 'reply_to'
  Représente la valeur de l'adresse de réponse du message électronique. Une seule adresse de messagerie est acceptée comme valeur valide. Il s'agit d'un paramètre optionnel utilisé pour remplacer les paramètres dans le profil de messagerie. Ce paramètre est de type varchar(MAX). Les paramètres de sécurité SMTP déterminent si ces substitutions sont acceptées. Si aucun paramètre n'est spécifié, la valeur par défaut est NULL.

• [ @subject= ] 'subject'
  Objet du message électronique. L'objet est de type nvarchar(255). Si l'objet est omis, « Message SQL Server » est la valeur par défaut.

• [ @body= ] 'body'
  Corps du message électronique. Le corps du message électronique est de type nvarchar(max), avec NULL comme valeur par défaut.

• [ @body_format= ] 'body_format'
  Format du corps du message. Le paramètre est de type varchar(20), avec NULL comme valeur par défaut. Lorsqu'il est spécifié, les en-têtes du message sortant sont définis pour indiquer le format choisi pour le corps de message. Ce paramètre peut contenir l'une des valeurs suivantes :

  • TEXT

  • HTML

  La valeur par défaut est TEXT.

• [ @importance= ] 'importance'
  L'importance du message. Le paramètre est de type varchar(6). Il peut contenir l'une des valeurs suivantes :

  • Low

  • Normal

  • High

  La valeur par défaut est Normal.

• [ @sensitivity= ] 'sensitivity'
  Le critère de diffusion du message. Le paramètre est de type varchar(12). Il peut contenir l'une des valeurs suivantes :

  • Normal

  • Personal

  • Private

  • Confidential

  La valeur par défaut est Normal.

• [ @file_attachments= ] 'file_attachments'
  Liste des noms de fichiers à joindre au message électronique (délimitée par des points-virgules). Les fichiers de la liste doivent être spécifiés sous forme de chemins d'accès absolus. La liste des pièces jointes est de type nvarchar(max). Par défaut, la messagerie de base de données limite la taille des pièces jointes à 1 Mo par fichier.

• [ @query= ] 'query'
  Requête à exécuter. Les résultats de la requête sont inclus dans le corps du message électronique ou attachés comme pièce jointe. La requête est de type nvarchar(max) et peut contenir des instructions Transact-SQL valides. Remarquez que la requête est exécutée dans une session distincte : les variables locales du script appelant la procédure stockée sp_send_dbmail sont donc indisponibles pour la requête.

• [ @execute_query_database= ] 'execute_query_database'
  Contexte de base de données dans lequel la procédure stockée exécute la requête. Le paramètre est de type sysname, avec la base de données active comme valeur par défaut. Ce paramètre est applicable seulement si l'argument @query est spécifié.

• [ @attach_query_result_as_file= ] attach_query_result_as_file
  Indique si l'ensemble de résultats de la requête est retourné comme pièce jointe. attach_query_result_as_file est de type bit, avec 0 comme valeur par défaut.

  Lorsque la valeur est 0, les résultats de la requête sont inclus dans le corps du message électronique, après le contenu du paramètre @body. Lorsque la valeur est 1, les résultats sont retournés comme pièce jointe. Ce paramètre est applicable seulement si l'argument @query est spécifié.

• [ @query_attachment_filename= ] query_attachment_filename
  Précise le nom de fichier à utiliser pour la pièce jointe de l'ensemble de résultats de la requête. query_attachment_filename est de type nvarchar(255), avec NULL comme valeur par défaut. Ce paramètre est ignoré lorsque attach_query_result a la valeur 0. Lorsque la valeur de attach_query_result est 1 et que ce paramètre est NULL, la messagerie de base de données crée un nom de fichier arbitraire.

• [ @query_result_header= ] query_result_header
  Spécifie si les résultats de la requête comportent des en-têtes de colonne. La valeur de query_result_header est de type bit. Lorsque la valeur est 1, les résultats de la requête contiennent des en-têtes de colonne, et lorsque la valeur est 0, les résultats n'incluent aucun en-tête de colonne. La valeur par défaut est 1. Ce paramètre est applicable seulement si l'argument @query est spécifié.

• [ @query_result_width = ] query_result_width
  Largeur de la ligne en caractères, à utiliser pour la mise en forme des résultats de la requête. L'argument query_result_width est de type int, avec 256 comme valeur par défaut. La valeur fournie doit être comprise entre 10 et 32767. Ce paramètre est applicable seulement si l'argument @query est spécifié.

• [ @query_result_separator= ] 'query_result_separator'
  Caractère utilisé pour séparer les colonnes dans la sortie de la requête. Le séparateur est de type char(1). La valeur par défaut est ' ' (espace).

• [ @exclude_query_output= ] exclude_query_output
  Spécifie si la sortie de l'exécution de la requête doit être retournée dans le message électronique. exclude_query_output est de type bit, avec la valeur par défaut 0. Lorsque la valeur de ce paramètre est 0, l'exécution de la procédure stockée sp_send_dbmail imprime le message retourné comme étant le résultat de l'exécution de la requête sur la console. Lorsque ce paramètre est défini sur 1, l'exécution de la procédure stockée sp_send_dbmail n'imprime aucun des messages d'exécution de la requête sur la console.

• [ @append_query_error= ] append_query_error
  Indique si un message électronique doit être envoyé lorsqu'une erreur est retournée pour la requête spécifiée dans l'argument @query. append_query_error est de type bit, avec la valeur par défaut 0. Lorsque la valeur de ce paramètre est 1, la messagerie de base de données envoie le message électronique et inclut le message d'erreur de la requête dans le corps du message. Lorsque la valeur de ce paramètre est 0, aucun message électronique n'est envoyé et la procédure sp_send_dbmail se termine avec le code de retour 1, ce qui indique un échec.

• [ @query_no_truncate= ] query_no_truncate
  Spécifie si la requête doit être exécutée avec l'option permettant d'éviter la troncation des types de données à variables importantes en longueur (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image et les types de données définis par l'utilisateur). Lorsqu'ils sont définis, les résultats de requête n'incluent pas les en-têtes de colonne. La valeur de query_no_truncate est de type bit. Lorsque la valeur est définie à 0 ou lorsqu'elle n'est pas spécifiée, les colonnes de la requête sont limitées à 256 caractères, si elle est définie à 1, les colonnes ne sont pas tronquées. La valeur par défaut de ce paramètre est 0.

  Notes

  Lorsque l'option @query_no_truncate est utilisée avec d'importants volumes de données, elle consomme des ressources supplémentaires et peut diminuer les performances du serveur.

• [ @query_result_no_padding ] @query\_result\_no\_padding
  Le type est bit. La valeur par défaut est 0. Lorsque vous définissez la valeur 1, les résultats de la requête ne sont pas complétés, ce qui peut réduire la taille du fichier. Si vous définissez @query\_result\_no\_padding sur 1 et que définissez le paramètre @query\_result\_width, le paramètre @query\_result\_no\_padding remplace le paramètre @query\_result\_width.

  Dans ce cas, aucune erreur ne se produit.

  Si vous affectez la valeur 1 à @query\_result\_no\_padding et que vous définissez le paramètre @query\_no\_truncate, une erreur est générée.

• [ @mailitem_id= ] mailitem_id [ OUTPUT ]
  Paramètre de sortie facultatif qui retourne l'élément mailitem_id du message. L'argument mailitem_id est de type int.

Valeurs des codes de retour

Un code de retour de 0 indique le succès. Une autre valeur signifie l'échec. Le code d'erreur de l'instruction qui a échoué est stocké dans la variable @@ERRROR.

Ensembles de résultats

En cas de succès, le message « Courrier en file d'attente » est renvoyé.

Notes

Avant d'être utilisée, la messagerie de base de données doit être activée à l'aide de l'Assistant Configuration de la messagerie de base de données ou de sp_configure.

sysmail_stop_sp interrompt le fonctionnement de la messagerie de base de données en arrêtant les objets Service Broker que le programme externe utilise. La procédure stockée sp_send_dbmail continue d'accepter du courrier tandis que la messagerie de base de données est arrêtée par le biais de sysmail_stop_sp. Pour démarrer la messagerie de base de données, utilisez sysmail_start_sp.

Lorsque l'argument @profile n'est spécifié, la procédure stockée sp_send_dbmail utilise un profil par défaut. Si l'utilisateur expéditeur du message électronique a un profil privé par défaut, la messagerie de base de données utilise ce profil. S'il ne dispose pas d'un tel profil, sp_send_dbmail utilise le profil public par défaut. Si aucun de ces deux profils n'existe, la procédure sp_send_dbmail renvoie une erreur.

sp_send_dbmail ne prend pas en charge les messages électroniques sans contenu. Pour envoyer un message électronique, vous devez spécifier au moins un des paramètres suivants : @body, @query, @file_attachments ou @subject. Sinon, la procédure bloquée sp_send_dbmail renvoie une erreur.

La messagerie de base de données utilise le contexte de sécurité Microsoft Windows de l'utilisateur actuel pour contrôler l'accès aux fichiers. L'utilisateur identifié grâce à l'authentification SQL Server ne peut donc pas insérer de fichiers joints à l'aide de l'argument @file_attachments. Windows n'autorise pas SQL Server à fournir des informations d'identification d'un ordinateur distant à un autre. De cette façon, la messagerie de base de données ne peut pas joindre de fichiers aux messages depuis un partage réseau lorsque la commande est exécutée par un ordinateur autre que celui sur lequel SQL Server s'exécute.

Si les deux arguments @query et @file_attachments sont spécifiés et que le fichier est introuvable, la requête est malgré tout exécutée, mais le message électronique n'est pas envoyé.

Lorsqu'une requête est spécifiée, l'ensemble de résultats se présente sous la forme d'un texte inséré. Les données binaires contenues dans le résultat sont envoyées au format hexadécimal.

Les paramètres @recipients, @copy_recipients et @blind_copy_recipients sont des listes d'adresses de messagerie électronique délimitées par des points-virgules. Au moins un de ces paramètres doit être défini, sinon la procédure sp_send_dbmail renvoie une erreur.

Lorsque la procédure sp_send_dbmail est exécutée sans contexte de transaction, la messagerie de base de données démarre et valide une transaction implicite. Lorsque la procédure sp_send_dbmail est exécutée dans une transaction existante, c'est l'utilisateur qui doit valider ou annuler les modifications. Aucune transaction interne n'est lancée.

Autorisations

Les autorisations d'exécution pour la procédure stockée sp_send_dbmail sont accordées par défaut à tous les membres du rôle de base de données DatabaseMailUser dans la base de données msdb. Toutefois, lorsque l'utilisateur expéditeur du message ne dispose pas de l'autorisation lui permettant d'utiliser le profil pour la requête, sp_send_dbmail retourne une erreur et n'envoie pas le message.

Exemples

A.Envoi d'un message électronique

Cet exemple montre l'envoi d'un message électronique à Dan Wilson à l'adresse de messagerie danw@Adventure-Works.com. Le message a comme objet Automated Success Message. Le corps du message contient la phrase 'The stored procedure finished successfully'.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

B.Envoi d'un message électronique avec les résultats d'une requête

Cet exemple montre l'envoi d'un message électronique à Dan Wilson à l'adresse de messagerie danw@Adventure-Works.com. Le message a comme objet Work Order Count et exécute une requête qui affiche le nombre d'ordres de travail pour lesquels une date DueDate ne dépasse pas le 30 avril 2004 de plus de deux jours. La messagerie de base de données joint le résultat au courrier sous la forme d'un fichier texte.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

C.Envoi d'un message électronique au format HTML

Cet exemple montre l'envoi d'un message électronique à Dan Wilson à l'adresse de messagerie danw@Adventure-Works.com. Le message a comme objet Work Order List et affiche un document HTML montrant les ordres de travail pour lesquels la valeur de DueDate ne dépasse pas le 30 avril 2004 de plus de deux jours. La messagerie de base de données envoie le message au format HTML.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Voir aussi

Référence

Procédures stockées de messagerie de base de données (Transact-SQL)

sp_addrolemember (Transact-SQL)

Concepts

Messagerie de base de données

Objets de configuration de la messagerie de base de données