Manual Correos desde SQL Server (SQL Mail Server)
1 Objetivo del documento
2 Instalación servidor de correos SQL server.
2.1 Consideraciones
2.2 Crear un perfil y una cuenta de correo
2.3 Configurar y activar el correo.
3 Creación de tabla de configuración.
4 Creación de procedimiento almacenado.
5 Creación de tarea programada.
6 Verificar funcionamiento del servidor de correo
1 Objetivo del documento
Este documento permite ilustrar los pasos para la implementación del sistema de notificación de vencimiento de licencias para ilustrar el envío de mensajes de correo desde el servidor MSSQL.
2 Instalación servidor de correos SQL server.
2.1 Consideraciones
Debe verificarse que el servicio “SQL Server Agent” este iniciado y automático, esto puede hacerlo ingresando al Panel de control (Control Panel), luego herramientas administrativas (Administrative Tools), en la lista de elementos seleccionar servicios (Services). Luego buscar el servicio que corresponde a este componente e iniciarlo y ponerlo automático como muestra la figura siguiente:
2.2 Crear un perfil y una cuenta de correo
En el programa de administración de la base de datos “Microsoft SQL Server Management Studio”
Seleccionamos el grupo Management y luego la opción Database Mail como muestra en la figura siguiente:
Luego aparece la ventana de bienvenida, con el objetivo de guiarnos a crear un perfil para el envió de correos.
Luego seleccionamos la opción que deseamos, en este caso vamos a crear un perfil nuevo, pero luego que se tenga un perfil creado, se puede adicional a esto editar perfiles creados y actualizar la información.
Se crea el nuevo perfil y se le asocia una cuenta de correo para envío.
En la siguiente imagen se muestra los datos para asignar la cuenta de correo a este perfil.
Luego de creada la cuenta, las siguientes pantallas sirven para confirmar y finalizar el proceso, las colocamos para que sirvan de ilustración del proceso.
Seleccionar la cuenta que vas a usar en este perfil, esto por si se tiene otras cuentas creadas, como muestra la figura siguiente:
Parámetros para comportamiento del perfil.
Al final se confirma la información y se finaliza.
2.3 Configurar y activar el correo.
Se debe abrir una pantalla nueva para ejecutar un comando SQL, y se ejecutan los comandos siguientes, adicional a esto colocamos una imagen con el resultado.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
3 Creación de tabla de configuración.
Con las siguientes instrucciones se crea la base de datos de configuración, la cual permite almacenar los parámetros para la notificación y el correo al cual se va a notificar.
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Configuracion]( [FirstNoticeDays] [int] NULL, [SecondNoticeDays] [int] NULL, [FinalNoticeDays] [int] NULL, [MailNotificationGroup] [varchar](100) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
4 Creación de procedimiento almacenado.
Se debe seleccionar en la base de datos en la que se está trabajando y seleccionar en los grupos de objetos “Programmability” luego “Store Procedure” y luego clic derecho, entonces seleccionar la primera opción “New Store Procedure” como muestra la figura siguiente:
Luego pegar el código que colocamos a continuación:
/****** Object: StoredProcedure [dbo].[SP_NotificarVencimientoLicencias] Script Date: 11/22/2012 09:50:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================
-- Author: Consulta única
-- Create date: 2012.11.22
-- Description: Notificar vencimiento de licencias
-- ==============================================================
ALTER PROCEDURE [dbo].[SP_NotificarVencimientoLicencias]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExpirationDate datetime,
@Nombre varchar(50),
@MailNotificationGroup varchar(100),
@Mensaje varchar(100),
@FirstNoticeDays int,
@SecondNoticeDays int,
@FirstNoticeDaysCompleted bit,
@SecondNoticeDaysCompleted bit,
@StringSQL varchar(500),
@id_License int,
@id_Cliente int
DECLARE Notificar_cursor CURSOR FOR
SELECT ExpirationDate,Cliente.Nombre, configuracion.MailNotificationGroup,
FirstNoticeDays,SecondNoticeDays, License.FirstNoticeDaysCompleted,
License.SecondNoticeDaysCompleted, License.id_License, License.id_Cliente
from dbo.License
inner join Cliente on License.id_Cliente = Municipio.id_Cliente
CROSS JOIN configuracion
where CurrentCliente_i is not null and
InstallationDate is not null and
ExpirationDate > GETDATE() and
(( ExpirationDate = (GETDATE() + configuracion.FirstNoticeDays) ) or
( ExpirationDate = (GETDATE() + configuracion.SecondNoticeDays) ) or
( ExpirationDate - (GETDATE() + FinalNoticeDays) <= FinalNoticeDays ) )
OPEN Notificar_cursor;
FETCH NEXT FROM Notificar_cursor INTO @ExpirationDate, @Nombre, @MailNotificationGroup, @FirstNoticeDays,
@SecondNoticeDays,@FirstNoticeDaysCompleted,@SecondNoticeDaysCompleted, @id_License, @id_Cliente
WHILE @@FETCH_STATUS = 0
BEGIN
set @Mensaje = 'La licencia del cliente ' + @Nombre + ', expira en : ' + convert(varchar(20),@ExpirationDate )
EXEC msdb.dbo.sp_send_dbmail @profile_name='Phoenix',
@recipients=@MailNotificationGroup,
@subject=@Mensaje,
@body= @Mensaje
if @FirstNoticeDaysCompleted = 0 and (@ExpirationDate <= (GETDATE() + @FirstNoticeDays ))
begin
SET @StringSQL = ''
SET @StringSQL = ' Update License Set FirstNoticeDaysCompleted = 1 '
+ ' where id_License = ' + Str(@id_License) + ' and id_Municipio = ' + Str(@id_Municipio)
EXEC( @StringSQL )
end
if @SecondNoticeDaysCompleted = 0 and (@ExpirationDate <= (GETDATE() + @SecondNoticeDays ))
begin
SET @StringSQL = ''
SET @StringSQL = ' Update License Set SecondNoticeDaysCompleted = 1 '
+ ' where id_License = ' + Str(@id_License) + ' and id_cliente = ' + Str(@id_cliente)
EXEC( @StringSQL )
end
FETCH NEXT FROM Notificar_cursor INTO @ExpirationDate, @Nombre, @MailNotificationGroup, @FirstNoticeDays,
@SecondNoticeDays,@FirstNoticeDaysCompleted,@SecondNoticeDaysCompleted, @id_License, @id_Cliente;
END
CLOSE Notificar_cursor;
DEALLOCATE Notificar_cursor;
end
5 Creación de tarea programada.
Para estos fines, debemos ubicar en la lista de elementos “SQL Server Agent”, expandirlo como muestra la figura siguiente.
Luego se presiona clic derecho encima de la opción “Jobs”, y en el menú contextual seleccionar la opción “New Job”, como muestra la figura siguiente.
Se ingresan los datos generales de la tarea y una descripción opcional.
Se selecciona la opción “Step” para definir las cosas que ejecutara nuestra tarea y en que secuencia, si se va a agregar una nueva se pulsa el botón “Insert”, o se puede editar una existente, como muestra la figura siguiente.
Luego ponemos la información importante de nuestro paso, descripción, base de datos y en nuestro caso particular el comando que va a ejecutar, en este caso es el procedimiento almacenado que creamos previamente en los pasos de este documento, al final la información se muestra de la siguiente manera.
Ahora definimos en qué momento se va a ejecutar la tarea, utilizando la opción “Schedules”, donde definimos los tiempos y las fechas de ejecución, al igual que en los trabajos podemos definir nuevas o editar las ya existentes, como muestra la figura siguiente.
Se determina entonces cuando se ejecutara la tarea, de acuerdo a los parámetros que especifiquemos en la pantalla siguiente.
6 Verificar funcionamiento del servidor de correo
Se puede validar y hasta ver un registro de todos los correos enviados por el servidor de correo, utilizando las siguientes instrucciones en una ventana de comandos en el “Server Managment Studio”.
use msdb; SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO
