locked
Email not going . Using SqlExpress RRS feed

  • Question

  • User1979860870 posted

    Hi

    Use Test
    GO
    /****** Object:  StoredProcedure [dbo].[sp_DAD_Mail_RejectedAnnexure0]    Script Date: 12/07/2019 10:32:51 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    	DECLARE @Store VARCHAR(10);
    	DECLARE @tableHTML  NVARCHAR(MAX) ;
    	DECLARE @MailSubject  NVARCHAR(MAX) ;
    	DECLARE @MailHeader  NVARCHAR(MAX) ;
    	DECLARE @RowCount int ;
    	DECLARE @i int = 1;
    
    
    	select @RowCount =  Count(*) FROM Test T0  
    	INNER JOIN Test1 T1 ON T0.[Card]=T1.[Card]
    	INNER JOIN Test2 T2 ON T1.[Slp] = T2.[Slp] 
    	WHERE DateDiff(DD,T0.[Date],GetDate())=10 
    
    	Set @MailSubject='Value as on '+ CONVERT(VARCHAR(10),GETDATE(),103)+'';
    	
    
    	while @i <= @RowCount 
    	BEGIN
    
    		SET @tableHTML =@MailHeader+
    		N'<table border="1" style="font-size:12px;border: 1px solid #d4d4d4">' +
    		N'<tr><th>SN.</th><th>Store</th><th>Document No</th><th>Date</th>' +
    		N'<th>Customer Name</th><th>Amount</th><th>Remarks</th><th>Url</th>' +
     
    		replace(REPLACE(CAST ( ( SELECT td = ROW_NUMBER() OVER(ORDER BY T0.[Date]), '',
    						td = T2.[Slp], '',
    						td = T0.[Date], '',
    						td = T1.[Card], '', 
    						td = T0.[Sum]
    				  FROM Test T0  
    					INNER JOIN Test1 T1 ON T0.[Card]=T1.[Card]
    	INNER JOIN Test2 T2 ON T1.[Slp] = T2.[Slp] 
    	WHERE DateDiff(DD,T0.[Date],GetDate())=10 
    				  FOR XML PATH('tr'), TYPE 
    		) AS VARCHAR(MAX) ),'&lt;','<'),'&gt;','>')   ;
    
    		EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notify, @recipients='test@yahoo.com',
     
    		@subject = @MailSubject,
    		@body = @tableHTML,
    		@body_format = 'HTML' ;
    		SET @i = @i + 1;
    	END
    go

    When i execute below statement then it works

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notifiy',
    @recipients = 'test@yahoo.com',
    @body = 'The database mail configuration was completed successfully.',
    @subject = 'Automated Success Message';
    GO

    Thanks

    Wednesday, August 26, 2020 4:53 PM

Answers

  • User-474980206 posted

    I would assume the query returned no rows, so you are trying to send with an empty body.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2020 8:02 PM