martes, 6 de noviembre de 2012

Automatizar el envío de correo electrónico con SQL Server (parte 1)

El propósito de este artículo es describir un método para automatizar el envío de correos electrónicos a partir de información disponible en una base de datos SQL Server.

En este primer artículo, explicaremos cómo configurar el servicio de DatabaseMail de SQL Server 2008 R2 Express. Probaremos dos configuraciones:

  • Una con un servicio gratuito como GMail
  • Otra con un servicio de correo provisto por un Hosting, en este caso BlueHost

En un segundo artículo, vamos a explicar como enviar los datos a partir de información disponible en nuestra base de datos, cómo darle formato al correo y cómo automatizar su envío.

Comencemos… 

 

1. Habilitar DatabaseMail en SQL Server 2008 R2 Express

En la versión Express de SQL Server no disponemos del asistente para configurar DatabaseMail, por lo cual deberemos hacer el trabajo en forma manual. Primero abrimos SQL Server Management Studio y nos conectamos:

image

Con el botón derecho del mouse sobre el nombre de nuestro servidor ir a la opción Facets. Es posible que esta opción se demore en abrir:

image

Luego vamos a la opción Surface Area Configuration / DatabaseMailEnabled.

Ponemos el valor True.

image

Luego reiniciamos SQL Server con el botón derecho del mouse sobre el servidor, haciendo clic en Restart.

image

 

2. Configurar DatabaseMail para ser usado con GMail

Dentro de la base msdb vamos a utilizar un conjunto de procedimientos almacenados para configurar el correo, en este caso usando GMail como SMTP:

sysmail_add_account_sp  
     @account_name =  'Prueba',
     @email_address =  'micuenta@gmail.com' ,
     @display_name =  'Prueba DataBaseMail' ,
     @replyto_address =  'noresponder@gmail.com' ,
     @mailserver_name =  'smtp.gmail.com',
     @mailserver_type =  'SMTP' ,
     @port =  587,
     @username =  'micuenta@gmail.com',
     @password =  '-----',
     @enable_ssl =  TRUE

sysmail_add_profile_sp @profile_name = 'Profile de prueba'

sysmail_add_profileaccount_sp
    @profile_name = 'Profile de prueba' ,
    @account_name = 'Prueba',
    @sequence_number = 1

 

3. Enviar un correo de prueba

Para enviar un correo de prueba, podemos utilizar un procedimiento como el siguiente, siempre dentro de la base de datos msdb:

EXEC sp_send_dbmail @profile_name='Profile de prueba',
@recipients='jpussacq@gmail.com',
@subject='Mensaje de prueba',
@body='Mi primer prueba de Database Mail'

Y obtendremos un mensaje como el siguiente:

Mail queued.

Si todo funcionó bien, recibiremos el correo sin problemas. Sino, podemos explorar algunas de las opciones de la siguiente sección.

 

4 Análisis de problemas

Las siguientes consultas, nos pueden ayudar a hacer un análisis de los problemas y a conocer en qué estado quedaron nuestros correos:

select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems

Esta consulta nos puede brindar más información si el estado es failed:

SELECT
items.subject,
items.last_mod_date,
l.description
FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id

Si reciben un error como el siguiente “The server response was: 5.7.0 Must issue a STARTTLS command first”, lo más probable es que hayamos olvidado configurar la opción de SSL en la cuenta.

 

5. Configurar DatabaseMail para ser usado con BlueHost

Es similar al caso de Gmail, pero con las opciones de Blue Host. 

USE msdb
GO
sysmail_add_account_sp  
     @account_name =  'Blue Host Mail',
     @email_address =  'cuenta@midominio.com' ,
     @display_name =  '---' ,
     @replyto_address =  'noresponder@---' ,
     @mailserver_name =  'mail.midominio.com',
     @mailserver_type =  'SMTP' ,
     @port =  26,
     @username =  'cuenta@midominio.com',
     @password =  '---',
     @enable_ssl =  FALSE
GO
sysmail_add_profile_sp @profile_name = 'Perfil Blue Host'
GO
sysmail_add_profileaccount_sp
    @profile_name = 'Perfil Blue Host' ,
    @account_name = 'Blue Host Mail',
    @sequence_number = 1
GO
EXEC sp_send_dbmail @profile_name='Perfil Blue Host',
@recipients='jpussacq@gmail.com',
@subject='Mensaje de prueba',
@body='Este es un mensaje de prueba del sistema'

 

Artículos relacionados:

11 comentarios:

Hola, gran articulo, sabes como indicar que acepte cuelquier certificado ya que estoy apuntando a mi Servidor Correo el cual lo uso con SSL y un Certificado Autofirmado.

Hola, al momento de ejecutar me sale el siguiente mensaje:

Msg 2627, Level 14, State 1, Procedure sysmail_add_account_sp, Line 33
Violation of UNIQUE KEY constraint 'SYSMAIL_ACCOUNT_NameMustBeUnique'. Cannot insert duplicate key in object 'dbo.sysmail_account'.
The statement has been terminated.

¿Es posible que hayas tratado de crear dos veces la misma cuenta?

Mmm no sé lo de certificados. Para SSQL puedes usar el atributo @enable_ssl del procedimiento sysmail_add_account_sp

Más info en: http://msdn.microsoft.com/en-us/library/ms182804(v=sql.105).aspx

Hola, me sale el siguiente error, pero al momento de realizar el ping desde el servidor me resuelve, pero no me deja sacar el correo.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-06-21T10:32:11). Exception Message: Could not connect to mail server. (Se produjo un error durante el intento de conexión ya que la parte conectada no respondió adecuadamente tras un periodo de tiempo, o bien se produjo un error en la conexión establecida ya que el host conectado no ha podido responder 74.125.196.109:465). )

Se puede usar con cuentas Hotmail ?, hay una segunda parte del articulo ?

Hola, no hay segunda parte. Fijate que en una de las referencias explica como usarlo con hormail. Quizá te ayude. Saludos!

http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Buen día disculpa como se puede hacer para mandar el correo a una especifica, de antemano gracias.

Buen día, funciona muy bien, pero disculpa como se puede hacer para mandar el correo a una hora y fecha especifica, de antemano gracias

Hola! Podés programar un job o tarea automática en SQL, que se ejecute en fecha y hora que vos determines y coloques como acción el comando EXEC sp_send_dbmail.

Publicar un comentario en la entrada