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 :

Article lu   fois.

L'auteur

Site personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

La table T_DESTINATAIRE_DST contient les informations suivantes :

 
Sélectionnez
[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 :

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

La table T_TYPE_ENVOI_TEV contient les informations suivantes :

 
Sélectionnez
[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 :

 
Sélectionnez
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 :

 
Sélectionnez
[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 :

 
Sélectionnez
[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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
SET @From='!!!' 

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

 
Sélectionnez
-- 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

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

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'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.