I have an issue, I have a schedule Job, which send Email.
When I started the Job, runs correctly and sends me the email, but when the job it runs by itself it seems was successfully, but does not send me any email, I looked history Job and I found this:
"Executed as dbo. mail queued [SQLSTATE 01000][Message 0] step correctly concluded"
Any idea why is this?
the code is below if there is a problem whith it
ALTER PROCEDURE [dbo].[spJobMail]
AS
BEGIN
DECLARE @rslt INT
,@row INT
,@count INT
,@mess VARCHAR(8000)
DECLARE @clint INT
,@name VARCHAR(250)
,@mail VARCHAR(50)
DECLARE @dive VARCHAR(50)
,@marc VARCHAR(50)
,@type VARCHAR(50)
,@vin VARCHAR(20)
,@maId INT
,@tyId INT
,@lstId INT
,@last DATETIME
DECLARE Cliente CURSOR
FOR
SELECT C.Cli_ID
,Cli_Nom
,Cli_Cor
FROM Cliente C --INNER JOIN Tmp_DisClient DC ON C.Cli_ID = DC.Cli_ID
UPDATE Historial_DisNoReport
SET Fch_Event = GETDATE()
SET @row = 0
OPEN Cliente
FETCH NEXT FROM Cliente INTO @clint, @name, @mail
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE EnvioCorreo CURSOR
FOR
SELECT DC.Dis_Sky
,M.Des_Mar
,M.Mar_ID
,T.Des_Tip
,T.Tip_ID
,LastTime
,VIN
FROM Tmp_DisClient DC INNER JOIN Cliente C ON DC.Cli_ID = C.Cli_ID
INNER JOIN Marca M ON DC.Mar_ID = M.Mar_ID
INNER JOIN Tipo T ON DC.Tip_ID = T.Tip_ID
INNER JOIN Dispositivo D ON DC.Dis_Sky = D.Dis_Sky
WHERE C.Cli_ID = @clint
SELECT @count = COUNT(Tmp_ID)
FROM Tmp_DisClient
WHERE Cli_ID = @clint
SET @mess = '<style> .datagrid table{ border-collapse: collapse; text-align: left; width: 80%;}'
SET @mess = @mess + '.datagrid table td, .datagrid table th { padding: 3px 10px; }'
SET @mess = @mess + '.datagrid table thead th {background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #991821), color-stop(1, #80141C) ); background:-moz-linear-gradient( center top, #991821 5%, #80141C 100% ); filter:progid:DXImageTransform.Microsoft.gradient(startColorstr=''#991821'', endColorstr=''#80141C''); background-color:#991821; color:#FFFFFF; font-size: 15px; font-weight: bold; border-left: 1px solid #B01C26; } '
SET @mess = @mess + '.datagrid table thead th:first-child { border: none; } .datagrid table tbody td { color: #80141C; border-left: 1px solid #F7CDCD; font-size: 12px; font-weight: normal; }'
SET @mess = @mess + '.datagrid table tbody .alt td { background: #F7CDCD; color: #80141C; } .datagrid table tbody td:first-child { border-left: none; } .datagrid table tbody tr:last-child td { border-bottom: none; } .datagrid table tfoot td div { border-top: 1px solid #991821; background: #F7CDCD; } .datagrid table tfoot td { padding: 0; font-size: 12px; } .datagrid table tfoot td div{ padding: 2px; }'
SET @mess = @mess + '.datagrid table tfoot td ul { margin: 0; padding:0; list-style: none; text-align: right; } .datagrid table tfoot li { display: inline; }'
SET @mess = @mess + '.datagrid table tfoot li a { text-decoration: none; display: inline-block; padding: 2px 8px; margin: 1px; color: #FFFFFF; border: 1px solid #991821; -webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #991821), color-stop(1, #80141C) ); background:-moz-linear-gradient( center top, #991821 5%, #80141C 100% ); filter:progid:DXImageTransform.Microsoft.gradient(startColorstr=''#991821'', endColorstr=''#80141C''); background-color:#991821; }'
SET @mess = @mess + '.datagrid table tfoot ul.active, .datagrid table tfoot ul a:hover { text-decoration: none; border-color: #80141C; color: #FFFFFF; background: none; background-color:#991821; } div.dhtmlx_window_active, div.dhx_modal_cover_dv { !important; } </style>'
SET @mess = @mess + '<table width="100%" border="0"> <tr> <td></td> <td colspan="3" align="center"> <img src="header.png" width="100%" /> </td> <td></td> </tr>'
SET @mess = @mess + '<tr> <td> </td> <td colspan="3" align="center">'
SET @mess = @mess + '<table width="80%" border="0">'
SET @mess = @mess + '<tr> <td>'
SET @mess = @mess + '<p style="text-align:justify"> Estimado Usuario <br /> <br />'
SET @mess = @mess + 'Le informamos que cierta(s) unidad(es) estn presentando un detalle en el tiempo de reporte y no estn funcionando adecuadamente. <br />'
SET @mess = @mess + 'Sugerimos que se comunique con su ejecutivo de cuenta y/o al rea de call center al telfono: (55) 5263 5390, Ext 280, donde con gusto le informaran sobre el proceso para agendar una revisin la unidad. </p>'
SET @mess = @mess + '</td> </tr> </table> </td> <td> </td> </tr>'
SET @mess = @mess + '<tr> <td></td> <td align="center" colspan="3">'
SET @mess = @mess + '<div class="datagrid" style=" width:80%">'
SET @mess = @mess + '<table> <thead> <tr> <th>VIN</th> <th>ltimo da de Reporte</th> <th>Marca</th> <th>Tipor</th> </tr> </thead>'
SET @mess = @mess + '<tbody>'
OPEN EnvioCorreo
FETCH NEXT FROM EnvioCorreo INTO @dive, @marc, @maId, @type, @tyId, @last, @vin
WHILE @@FETCH_STATUS = 0
BEGIN
SET @row = @row + 1
IF EXISTS(SELECT His_ID FROM Historial_DisNoReport WHERE Cli_ID = @clint AND Dis_Sky = @dive)
BEGIN
UPDATE Historial_DisNoReport
SET Fch_Event = @last
WHERE Cli_ID = @clint AND Dis_Sky = @dive
END
ELSE
BEGIN
SET @lstId = (SELECT CASE WHEN MAX(His_ID) IS NULL THEN 0
ELSE MAX(His_ID) + 1
END
FROM Historial_DisNoReport
WHERE Cli_ID = @clint)
INSERT INTO Historial_DisNoReport VALUES (@clint, @lstId, @dive, @maId, @tyId, @last, @vin)
END
IF @row%2 = 0
SET @mess = @mess + '<tr class="alt">'
ELSE
SET @mess = @mess + '<tr>'
SET @mess = @mess + '<td>' + @vin + '</td>'
SET @mess = @mess + '<td>' + CONVERT(VARCHAR, @last, 113) + '</td>'
SET @mess = @mess + '<td>' + @marc + '</td>'
SET @mess = @mess + '<td>' + @type + '</td>'
SET @mess = @mess + '</tr>'
FETCH NEXT FROM EnvioCorreo INTO @dive, @marc, @maId, @type, @tyId, @last, @vin
END -- Segundo Cursor
IF @count > 0
BEGIN
--SET @mess = @mess + '<tfoot> <tr> <td colspan="4"><div id="paging"><ul></ul></div></tr> </tfoot>'
SET @mess = @mess + '</tbody> </table> </div>'
SET @mess = @mess + '</td> <td></td> </tr>'
SET @mess = @mess + '<tr> <td></td> <td align="center" colspan="3"> <table width="80%" border="0">'
SET @mess = @mess + '<tr> <td align="justify">'
SET @mess = @mess + '<p>Sin ms por el momento quedo a sus rdenes.</p> <p>NOTA: Favor de no responde a este correo, ya que esta notificacin fue creada automticamente.</p>'
SET @mess = @mess + '</td> </tr> </table> </td> <td></td> </tr>'
SET @mess = @mess + '<tr> <td></td> <td colspan="3" align="center"> <img src="footer.png" /> </td> <td></td> </tr> </table>'
EXEC @rslt = msdb..sp_send_dbmail @profile_name = 'Notificaciones'
,@file_attachments = 'C:\Users\sky_program\Documents\SkyJackReporte\footer.png;C:\Users\sky_program\Documents\SkyJackReporte\header.png'
,@recipients = 'notificaciones@skyjack.com.mx'
,@body = @mess
,@body_format = 'HTML'
,@subject = 'Notificacin de Dispositivo'
--SELECT @mess
FETCH NEXT FROM Cliente INTO @clint, @name, @mail
END
CLOSE EnvioCorreo
DEALLOCATE EnvioCorreo
END -- Primer Cursor
CLOSE Cliente
DEALLOCATE Cliente
TRUNCATE TABLE Tmp_DisClient
END
Other recent topics
