Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000

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

***

Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

Une réflexion au sujet de « Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000 »

  1. Avatar de mikedavemmikedavem

    Bonjour Frédéric,

    J’ai eu à utiliser les procédure sp_ole pour envoyer des mails via sqlserver 2000. Ton script va très bien mais cependant il n’existe plus beaucoup d’entreprises qui permettent l’envoi de mail sans authentification sur leur smtp.

    3 paramètres supplémentaires me semblent importants : (voici le bout de script que j’utilise dans mon cas pour utiliser l’authentification smtp)

    IF @isAuthRequired = 1
    BEGIN
    EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.Fields(« http://schemas.microsoft.com/cdo/configuration/smtpauthenticate »).Value’,’1′
    — authentification login
    EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(« http://schemas.microsoft.com/cdo/configuration/sendusername »).Value’, @loginAuth
    — authentification password
    EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(« http://schemas.microsoft.com/cdo/configuration/sendpassword »).Value’, @passwdAuth
    END

    ++

Laisser un commentaire