Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000 - Club d'entraide des développeurs francophones

Date de publication : 26/06/2009 , Date de mise à jour : 26/06/2009

Il est possible de s'affranchir d'Exchange et d'Outlook afin de lancer un email dans SQL Server 2000 à condition de passer par les objets OLE manipulables par les procédures sp_OLE... en utilisant la classe CDO.

Dans le script ci dessous, je vous propose de créer une base de données, DB_SENDMAIL, contenant 4 tables :

La table T_DESTINATAIRE_DST contient les informations suivantes :

[DST_ID] : identifiant destinataire  
[DST_CODE] : code du destinataire  
[DST_NOM] : nom du destinataire  
[DST_PRENOM] : prénom du destinataire  
[DST_EMAIL] : adresse de messagerie du destinataire

Vous pouvez l'alimenter ainsi par exemple :

INSERT INTO T_DESTINATAIRE_DST VALUES ('CB','BRUNIE','Catherine','catherine@guss.fr')

La table T_TYPE_ENVOI_TEV contient les informations suivantes :

[TEV_ID] : identifiant du type d'envoi  
[TEV_CODE] : code du type d'envoi (To, Cc ou Bcc)  
[TEV_LIBELLE] : libellé du type d'envoi (Principal, Copie, Copie cachée)

Vous pouvez l'alimenter ainsi par exemple :

INSERT INTO T_TYPE_ENVOI_TEV VALUES ('To','Principal')  
INSERT INTO T_TYPE_ENVOI_TEV VALUES ('Cc','Copie')  
INSERT INTO T_TYPE_ENVOI_TEV VALUES ('Bcc','Copie Cachée')

La table T_MESSAGE_MSG contient les informations suivantes :

[MSG_ID] : identifiant du message,  
[MSG_TITRE] : titre du message,  
[MSG_TEXTE] : texte du message,  
[MSG_DH_INSERE] : date d'ajout du message à la table,  
[MSG_DH_ENVOI] : date d'envoi du message,  
[MSG_FAILED] : code retour permettant d'indiquer que l'envoie du message à échouer

La table T_ENVOYE_EVO contient les informations suivantes :

[MSG_ID] : identifiant du message à envoyer (existant dans la table T_MESSAGE_MSG),  
  [DST_ID] : identifiant du destinataire de ce message (existant dans la table T_DESTINATAIRE_DST),  
  [TEV_ID] : identifiant du type d'envoi à réaliser (existant dans la table T_TYPE_ENVOI_TEV)

Pour alimenter les tables T_MESSAGE_MSG et T_ENVOYE_EVO, je vous propose la procédure P_MESSAGE_SET que vous pouvez utiliser ainsi par exemple :

P_MESSAGE_SET @TITRE='Titre de mon message Test',  
              @TEXTE='Texte de mon message de Test', 
              @DEST1='CB',@TEVCODE1='To'

Vous pouvez employer cette procédure dans vos programmes.

Pour faire en sorte que l'agent SQL Server envoie régulièrement les messages que vous avez préparés dans la table T_MESSAGE_MSG, programmez l'exécution régulière, par exemple toutes les 5 minutes, de la procédure P_MESSAGE_SEND.

Attention : pour fonctionner, cette procédure doit être modifiée. Vous devez remplacer '???' dans la partie de code suivant :

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields "http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '???'

par l'adresse IP ou le nom de votre serveur de messagerie...

Vous devez aussi indiquer l'adresse de l'expéditeur du message dans la partie de code suivante :

SET @From='!!!'

En guise de test, essayez d'exécuter la procédure dans l'analyseur de requête

-- Création de la base de données DB_SENDMAIL et de ses objets 
 
CREATE DATABASE DB_SENDMAIL; 
GO 
 
USE DB_SENDMAIL; 
GO 
 
CREATE TABLE [dbo].[T_DESTINATAIRE_DST] ( 
  [DST_ID] [int] IDENTITY (1, 1) NOT NULL , 
  [DST_CODE] [char] (3) COLLATE French_CI_AS NOT NULL , 
  [DST_NOM] [char] (32) COLLATE French_CI_AS NOT NULL , 
  [DST_PRENOM] [varchar] (25) COLLATE French_CI_AS NULL , 
  [DST_EMAIL] [varchar] (128) COLLATE French_CI_AS NOT NULL  
) ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[T_ENVOYE_EVO] ( 
  [MSG_ID] [int] NOT NULL , 
  [DST_ID] [int] NOT NULL , 
  [TEV_ID] [int] NOT NULL  
) ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[T_MESSAGE_MSG] ( 
  [MSG_ID] [int] IDENTITY (1, 1) NOT NULL , 
  [MSG_TITRE] [varchar] (128) COLLATE French_CI_AS NOT NULL , 
  [MSG_TEXTE] [varchar] (5000) COLLATE French_CI_AS NOT NULL , 
  [MSG_DH_INSERE] [datetime] NOT NULL , 
  [MSG_DH_ENVOI] [datetime] NULL , 
  [MSG_FAILED] [bit] NOT NULL  
) ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[T_TYPE_ENVOI_TEV] ( 
  [TEV_ID] [int] IDENTITY (1, 1) NOT NULL , 
  [TEV_CODE] [char] (3) COLLATE French_CI_AS NOT NULL , 
  [TEV_LIBELLE] [varchar] (20) COLLATE French_CI_AS NOT NULL  
) ON [PRIMARY] 
GO 
 
INSERT INTO T_TYPE_ENVOI_TEV VALUES ('To','Principal') 
INSERT INTO T_TYPE_ENVOI_TEV VALUES ('Cc','Copie') 
INSERT INTO T_TYPE_ENVOI_TEV VALUES ('Bcc','Copie Cachée') 
GO 
 
 
/***************** 
   Frédéric Brouard - SQL spot - 2008-10-01 
************** 
   Envoi d'un mail sans obligation de serveur de messagerie 
   en utilisant OleCom / CDOSYS 
*****************/ 
 
CREATE PROCEDURE [dbo].[P_MESSAGE_SEND] 
   AS 
   Declare @From varchar(128), @To varchar(128), @Subject varchar(128), 
           @Body varchar(5000), @Cc varchar(500), @Bcc varchar(500) 
 
   Declare @iMsg int 
   Declare @hr int 
   Declare @source varchar(255) 
   Declare @description varchar(500) 
   Declare @output varchar(1000) 
 
   Declare @ID_MSG INT 
 
--*** Creation de l'objet CDO.Message ****** 
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT 
 
--*** Configuration de l'objet message ****** 
-- Configuration d'un serveur SMTP distant. 
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' 
-- Nom du serveur et adresse IP 
-- Remplacez ??? par le nom du serveur SMTP ou son adresse IP 
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '???' 
-- Sauvegarde les infos 
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null 
 
-- regarder si des mails sont a envoyer 
   WHILE (SELECT COUNT(*) FROM T_MESSAGE_MSG WHERE MSG_DH_ENVOI IS NULL) > 0 
   BEGIN 
  -- si oui sélectionner le premier 
  SELECT TOP 1 @ID_MSG=MSG_ID, @Subject=MSG_TITRE ,@Body=MSG_TEXTE FROM T_MESSAGE_MSG WHERE MSG_DH_ENVOI IS NULL 
        -- Alimentation destinataire principal -- 
  SET @To='' 
  SELECT @To=@To + DST_EMAIL + ', '  
  FROM T_DESTINATAIRE_DST DST INNER JOIN T_ENVOYE_EVO EVO ON DST.DST_ID=EVO.DST_ID 
            INNER JOIN T_TYPE_ENVOI_TEV TEV ON EVO.TEV_ID=TEV.TEV_ID 
  WHERE MSG_ID=@ID_MSG AND UPPER(TEV_CODE)='TO' 
  SET @To=SUBSTRING(@To,1,LEN(@To)-1) 
     -- Alimentation destinataire secondaire -- 
  SET @Cc='' 
  SELECT @Cc=@Cc + DST_EMAIL + ', '  
  FROM T_DESTINATAIRE_DST DST INNER JOIN T_ENVOYE_EVO EVO ON DST.DST_ID=EVO.DST_ID 
            INNER JOIN T_TYPE_ENVOI_TEV TEV ON EVO.TEV_ID=TEV.TEV_ID 
  WHERE MSG_ID=@ID_MSG AND UPPER(TEV_CODE)='CC' 
  SET @Cc=SUBSTRING(@Cc,1,LEN(@Cc)-1) 
  -- Alimentation destinataire secondaire caché -- 
  SET @Bcc='' 
  SELECT @Bcc=@Bcc + DST_EMAIL + ', '  
  FROM T_DESTINATAIRE_DST DST INNER JOIN T_ENVOYE_EVO EVO ON DST.DST_ID=EVO.DST_ID 
            INNER JOIN T_TYPE_ENVOI_TEV TEV ON EVO.TEV_ID=TEV.TEV_ID 
  WHERE MSG_ID=@ID_MSG AND UPPER(TEV_CODE)='BCC' 
  SET @Bcc=SUBSTRING(@Bcc,1,LEN(@Bcc)-1) 
  
-- remplacez !!! par votre mail d'expéditeur 
  SET @From='!!!' 
  
-- Mise en place des paramètres du mail 
        EXEC @hr = sp_OASetProperty @iMsg, 'To', @To 
        EXEC @hr = sp_OASetProperty @iMsg, 'From', @From 
 
        IF @Cc<>'' EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @Cc 
        IF @Bcc <>'' EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @Bcc 
 
        EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject 
 
-- Type mime : si mail en HTML remplacez 'TextBody' par 'HTMLBody' 
        EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body 
        EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL 
 
-- Gestion de l'erreur 
        IF @hr <>0 
          UPDATE T_MESSAGE_MSG 
    SET MSG_DH_ENVOI=CURRENT_TIMESTAMP, 
        MSG_FAILED=1 
      WHERE MSG_ID=@ID_MSG 
     ELSE  
       UPDATE T_MESSAGE_MSG 
       SET MSG_DH_ENVOI=CURRENT_TIMESTAMP 
        WHERE MSG_ID=@ID_MSG 
 
-- destruction de l'objet après utilisation 
        EXEC @hr = sp_OADestroy @iMsg 
   END 
GO 
 
CREATE PROCEDURE P_MESSAGE_SET @TITRE VARCHAR(128), @TEXTE VARCHAR(5000), 
        @DEST1 CHAR(3), 
        @DEST2 CHAR(3), @TEVCODE2 CHAR(3), 
        @DEST3 CHAR(3), @TEVCODE3 CHAR(3), 
        @DEST4 CHAR(3), @TEVCODE4 CHAR(3) 
AS 
IF @TITRE IS NULL OR @TEXTE IS NULL OR @DEST1 IS NULL RETURN 
 
DECLARE @ID_MESSAGE INT 
 
BEGIN TRAN 
  -- INSERTION DU MESSAGE -- 
  INSERT INTO T_MESSAGE_MSG (MSG_TITRE, MSG_TEXTE) 
  VALUES (@TITRE, @TEXTE) 
  IF @@ERROR<>0 GOTO TRAITE_ERREUR 
  -- RECUPERATION ID MESSAGE --  
  SET @ID_MESSAGE=@@IDENTITY 
  -- INSERTION DESTINATAIRE PRIMAIRE -- 
  INSERT INTO T_ENVOYE_EVO  
  SELECT @ID_MESSAGE, 
               (SELECT DST_ID  
                FROM T_DESTINATAIRE_DST  
                WHERE UPPER(DST_CODE)=UPPER(@DEST1)),  
         (SELECT TEV_ID  
                FROM T_TYPE_ENVOI_TEV  
                WHERE UPPER(TEV_CODE)='TO') 
  IF @@ERROR<>0 GOTO TRAITE_ERREUR      
  -- INSERTION DESTINATAIRE SECONDAIRE -- 
        IF EXISTS(SELECT * FROM T_DESTINATAIRE_DST WHERE UPPER(DST_CODE)= UPPER(@DEST2)) 
  BEGIN 
    INSERT INTO T_ENVOYE_EVO  
                SELECT  @ID_MESSAGE, 
                        (SELECT DST_ID  
                         FROM T_DESTINATAIRE_DST  
                         WHERE UPPER(DST_CODE)=UPPER(@DEST2)),  
                        (SELECT TEV_ID  
                         FROM T_TYPE_ENVOI_TEV  
                         WHERE UPPER(TEV_CODE)=UPPER(@TEVCODE2)) 
    IF @@ERROR<>0 GOTO TRAITE_ERREUR      
  END 
  -- INSERTION DESTINATAIRE TERTIAIRE -- 
        IF EXISTS(SELECT * FROM T_DESTINATAIRE_DST WHERE UPPER(DST_CODE)= UPPER(@DEST3)) 
  BEGIN 
    INSERT INTO T_ENVOYE_EVO  
                SELECT  @ID_MESSAGE, 
                        (SELECT DST_ID  
                         FROM T_DESTINATAIRE_DST  
                         WHERE UPPER(DST_CODE)=UPPER(@DEST3)),  
                        (SELECT TEV_ID  
                         FROM T_TYPE_ENVOI_TEV  
                         WHERE UPPER(TEV_CODE)=UPPER(@TEVCODE3)) 
    IF @@ERROR<>0 GOTO TRAITE_ERREUR      
  END 
  -- INSERTION DESTINATAIRE QUATERNAIRE -- 
        IF EXISTS(SELECT * FROM T_DESTINATAIRE_DST WHERE UPPER(DST_CODE)= UPPER(@DEST4)) 
  BEGIN 
    INSERT INTO T_ENVOYE_EVO  
                SELECT  @ID_MESSAGE, 
                        (SELECT DST_ID  
                         FROM T_DESTINATAIRE_DST  
                         WHERE UPPER(DST_CODE)=UPPER(@DEST4)),  
                        (SELECT TEV_ID  
                         FROM T_TYPE_ENVOI_TEV  
                         WHERE UPPER(TEV_CODE)=UPPER(@TEVCODE4)) 
    IF @@ERROR<>0 GOTO TRAITE_ERREUR      
  END 
 
COMMIT TRAN 
RETURN 
 
TRAITE_ERREUR: 
ROLLBACK TRAN 
 
GO

Copyright ©2009 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.