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 »

mikedavem 4 août 2009 à 16 h 51 min

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

++

Connectez-vous pour répondre ↓

Laisser un commentaire Annuler la réponse.

Vous devez être connecté(e) pour rédiger un commentaire.

Identifiez-vous

Créer un compte

Le blog de SQLpro

Le SQL pour SQL Server, PostGreSQL et tous les autres SGBDR

Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000

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

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?