A veces, me encuentro que al intentar enviar un correo como el siguiente:
EXEC msdb.dbo.sp_send_dbmail @recipients ='diego@daf.com.ar',@subject='Asunto', @body = 'Detalle'
Con mensajes de error de este tipo:
Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 62 Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.
TITLE: Microsoft SQL Server Management Studio ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ ADDITIONAL INFORMATION: Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. (Microsoft SQL Server, Error: 14641) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server& ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=14641&LinkId=20476
Para solventarlo y como primer paso, verifico si esta configurado:
sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO
name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- Database Mail XPs 0 1 1 1
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
principal_id principal_name profile_id profile_name is_default ---------------- --------------- ---------- ------------ -------- 2 guest 6 SqlProfile 1
Como lo esta, reviso el estado:
EXEC msdb.dbo.sysmail_help_status_sp;
Status ------- STOPPED (1 row(s) affected)
Lo encuentro detenido.
Lo inicio:
EXEC msdb.dbo.sysmail_start_sp;
Command(s) completed successfully.
Lo verifico:
EXEC msdb.dbo.sysmail_help_status_sp;
Status ------- STARTED (1 row(s) affected)
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
queue_type length state last_empty_rowset_time last_activated_time ---------- ----------- ---------------------------------------------------------------- ----------------------- ----------------------- mail 0 RECEIVES_OCCURRING 2012-11-28 12:19:47.767 2012-11-28 12:19:39.867 (1 row(s) affected)
Y si finalmente, reviso los logs a ver que paso:
SELECT top 20 * FROM msdb.dbo.sysmail_event_log order by log_id desc;
Fuente Microsoft.com http://technet.microsoft.com/en-us/library/ms187540.aspx
