Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000

SQLPro

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 quatre tables.

- Titre : Envoyer un mail sans Exchange ni Outlook avec SQL Server 2000

- Auteur : SQLPro

- Parution : 26 juin 2009

- Licence : 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.

Sommaire

- I. Le script

I - Le script

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 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 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 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