Envío de correos dinámicos usando SQL DATABASE Mail
automatizando la lista de usuarios a enviar, el asunto, el cuerpo y el horario
de envío de los mismos usando JOBS.
1. El primer paso a realizar es habilitar la configuración del DATABASE Mail.
1. El primer paso a realizar es habilitar la configuración del DATABASE Mail.
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
2. Creamos una cuenta y perfil encargado que
contendrá el host, puerto y credenciales; también podríamos crear las cuentas
con el asistente pero prefiero modo SCRIPT.
exec msdb.dbo.sysmail_add_account_sp
@account_name = 'Prueba',
@email_address = 'prueba@gmail.com' ,
@display_name = 'Prueba DataBaseMail' ,
@replyto_address = 'prueba@gmail.com' ,
@mailserver_name = 'smtp.gmail.com',
@mailserver_type = 'SMTP' ,
@port = 587,
@username = 'prueba@gmail.com',
@password = '******',
@enable_ssl = TRUE
Go
msdb.dbo.sysmail_add_profile_sp @profile_name = 'profile de prueba'
Go
msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'profile de prueba' ,
@account_name = 'Prueba',
@sequence_number = 1
Go
3 . Si el servidor no requiere autentificación de
credenciales; al agregar una cuenta se omite la variable @password y la variable @usuario se deja con el valor NULL
exec msdb.dbo.sysmail_add_account_sp
@account_name = 'Prueba',
@email_address = 'prueba@gmail.com',
@display_name = 'Prueba Database Mail',
@replyto_address = 'prueba@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@mailserver_type = 'SMTP' ,
@port = 587,
@username = NULL
Go
4 . Verificamos la creación de las cuentas; nos dirigimos
a “MANAGEMENT” Al desplegar seleccionamos “DATABASE MAIL”, Clic derecho y “CONFIGURE DATABASE MAIL”
6. Comprobamos que se ha verificado correctamente nuestra cuenta
7.
Podemos realizar esta verificación con el
script:
Use msdb
Go
select * from
sysmail_profile
select * from sysmail_account
8.
Paso siguiente será crear nuestros procedimientos
almacenados que envíen dinámicamente correos electrónicos a diferentes
usuarios, con diferente asunto, cuerpo y a la vez poner estos procedimientos
almacenados en JOBS que ejecuten el envío a una hora determinada.
9.
He creado dos tablas de acuerdo al modelo de
negocio que estuve trabajando:
a.
Una que contenga el título, descripción o cuerpo
y la periodicidad que usaremos en el JOB(para hacerlo dinámico la hora de envío)
b.
Una tabla que contiene la lista de correos por ROL de empleado y/o Usuarios específicos
de acuerdo a una agrupación de tipo ejemplo: “Facturas Observadas, Facturas
Pendientes de Pago, Facturas Nuevas, etc”
Uds. Podrían adecuarlo para el modelo de negocio que
van a trabajar.
c.
Finalmente creamos un mantenimiento en nuestra
aplicación, donde podremos editar el asunto, cuerpo y la periodicidad por cada
flujo de proceso.
10.
Creamos un procedimiento almacenado que
seleccione todos los mails asociados a un proceso de envío, ya sea
por ROL o Usuario Especifico; creo también una variable de salida que me
almacenará la lista de correos en una
sola fila.
Como tengo una lista de correos divididas por fila lo
que hago es juntar todas los correos en una sola fila separarlos por “;” validar si el campo EMAIL asociado al usuario está
vacío; validar si el ROL/CARGO se encuentra activo o no.
Create proc
[dbo].[sp_PMT_Alerta_Factura_Observada]
(
@IdFactura int
,@Mail varchar(1000) = null output
)
as begin
WITH ListaMail as(
Select AC.IdAlertaCorreo,
case
when AC.Equipo = 'Gestor' then
(
select top 1
case when G.Email is null then '' else G.Email end
from Factura P
left join
UsuarioGrupoUsuario UG on P.IdGestor = UG.IdUsuarioGrupoUsuario
left join Usuario G on UG.IdUsuario = G.IdUsuario
where P.IdFactura = @IdFactura
order by P.CodigoOCTP desc
)
when AC.Equipo = 'Gestor Linea' then
(
select top 1
case when GL.Email is null then '' else GL.Email end
from Factura P
left join
UsuarioGrupoUsuario UGL on P.IdGestorLider = UGL.IdUsuarioGrupoUsuario
left join Usuario GL on UGL.IdUsuario = GL.IdUsuario
where P.IdFactura = @IdFactura
order by P.CodigoOCTP desc
)
when AC.Equipo = 'Jefe Factura Integrador'
then
(
select top 1
case when CEC.Email is null then '' else CEC.Email end
from Factura P
left join
UsuarioGrupoUsuario UCEC on P.IdEquipoCertificacion =
UCEC.IdUsuarioGrupoUsuario
left join Usuario CEC on UCEC.IdUsuario = CEC.IdUsuario
where P.IdFactura = @IdFactura
order by P.CodigoOCTP desc
)
Else
(Select U.Email from Usuario U
where U.IdUsuario
= AC.IdUsuario)
end Mail
from PMT_Alerta_Motivo AM
Join PMT_Alerta_Correos AC on AM.IdAlertaMotivo = AC.IdAlertaMotivo
where AM.IdAlertaMotivo = 3 and AC.Activo = 1
)
SELECT @Mail= ltrim(COALESCE(@Mail + ' ;', '')) +
Mail from ListaMail
where Mail <> ''
select @Mail as Mail
end
11.
Creamos un procedimiento para el envío de correo
dinámico por cada concepto; en este caso enviamos a los ROLES asociados, y
Usuarios específicos a las FACTURAS OBSERVADAS, agregando el código de Factura
al asunto para que pueda identificarse al leer.
Create procedure
[dbo].[sp_PMT_Alerta_Factura_Observada_Enviar]
as begin
declare @IdFactura as int
declare @Descripcion as varchar(200)
declare @contador as int
declare CURSORVC cursor for
select FP.[IdFactura], P.CodigoOCTP + '-' +
cast(P.Secuencial as varchar(10)) + ' ' +
cast(case P.NumeroFase when -1 then '' else 'Fase ' + cast(P.NumeroFase as varchar(20)) end as varchar(20))
+ ' ' + P.Desc_corta
Descripcion
From FlujoFactura FP
Join Flujo F on FP.IdFlujo = F.IdFlujo
Join Factura p on FP.IdFactura = P.IdFactura
where Actual = 1
and F.IdFlujo
= 25
SET @contador = 10
open CURSORVC
fetch next from CURSORVC into
@IdFactura, @Descripcion
while @@fetch_status
= 0
begin
DECLARE @subject VARCHAR(300);
--al asunto le
agregamos la descripción dinámica por ID
--y tambien la parte
configurable por los adm.
SET @subject = @Descripcion +' - '+ (select AsuntoAlerta from
PMT_Alerta_Motivo where IdAlertaMotivo = 3);
--al cuerpo le
agregamos la parte configurable por los adm.
DECLARE @body VARCHAR(3000);
SET @body = (select CuerpoAlerta from PMT_Alerta_Motivo where
IdAlertaMotivo = 3)
;
--ejecutamos el procedimiento que nos devuelve la
lista de
--usuarios
declare
@Usuarios varchar(1000)
execute
sp_PMT_Alerta_Factura_Observado @IdFactura, @Usuarios output
--escribimos el profile que creamos para envío de
correos.
EXEC msdb.dbo.sp_send_dbmail @profile_name='Profile de Prueba',
--Enviamos de forma dinamica los correos:
@recipients
= @Usuarios,
@subject =
@subject,
@body=@body
fetch next from CURSORVC into @IdFactura, @Descripcion
set @Usuarios = NULL
set
@contador = @contador +
1
end
close CURSORVC
deallocate CURSORVC
end
12.
Si deseamos verificar el envío de correos podríamos
ejecutar el procedimiento
[dbo].[sp_PMT_Alerta_Factura_Observada_Enviar] y
veremos como resultado lo siguiente
Y en la pestaña Messages:
(1 row(s) affected)
Mail queued.
(1 row(s) affected)
Mail queued.
(1 row(s) affected)
Mail queued.
(1 row(s) affected)
Mail queued.
(1 row(s) affected)
Mail queued.
.
.
.
13.
Podríamos dejarlo ahí y ejecutar el
procedimiento por demanda; pero seremos un poco más exigentes; recuerdan que les mencione un campo periodicidad arriba? Bueno crearemos un JOB que almacene
nuestro procedimiento almacenado [dbo].[sp_PMT_Alerta_Factura_Observada_Enviar];
el JOB se ejecutará de manera diaria a una determinada hora.
Mostraré las pantallas “STEP” Y “SCHEDULE” del JOB
En la Pestaña STEP Seleccionamos nuestra BD y el
procedimiento a ejecutar.
14.
Ahora en la tabla tengo almacenado el campo periodicidad que
puse como varchar(5), ahora sí quiero actualizar el horario de envío del JOB
ejecuto lo siguiente.
Primero para probar ejecutamos lo siguiente:
exec msdb.dbo.sp_update_schedule
@name = 'Factura'
,@Freq_type = 4
,@Freq_subday_type = 1
,@active_start_time = 150000 --se
ejecutara a las 3:00 PM
Go
Ahora lo haremos con un procedimiento Almacenado, sacando
el campo de la BD.
CREATE Procedure
[dbo].[sp_PMT_Alerta_FacturaObservada_Periodicidad_Update]
as begin
declare @StartTime as varchar(200)
set @StartTime = (Select case when
Periodicidad is null
then '010000'else replace(Periodicidad,':','')+ '00' End
from
pmt_alerta_motivo where IdAlertaMotivo = 3)
exec msdb.dbo.sp_update_schedule
@name =
'Factura'
,@Freq_type = 4
,@Freq_subday_type =
1
,@active_start_time =
@StartTime
End
15.
Listo ahora tenemos nuestro envío de correo
Automatizando casi todo. Desde la lista de los emails, asunto, cuerpo,
periodicidad.