locked
Dynamic SQL To Send An Email RRS feed

  • Question

  • I am attempting a cursor with dynamic sql to send an email. I do not get an error but my message is not sent either. Can someone assist with what exactly I need to do to use dynamic sql in this instance?

    DECLARE mailcursor CURSOR FOR
    SELECT DISTINCT(foxtrotcharlie) FROM [TestData]	
    
    OPEN mailcursor
    FETCH NEXT FROM mailcursor INTO @FTC
    	
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	set @tableHTML = cast( (
    		select td = '<font color="#000000" face="verdana" size="2">' + table + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( one as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( two as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( three as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( four as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( five as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( six as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( seven as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( eight as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( nine as varchar(30) ) + '</font></td><td><font color="#000000" face="verdana" size="2">' + cast( ten as varchar(50) )
    		from (
    			  select table = tbl,
    					 one = one, 
    					 two = two,
    					 three = three,
    					 four = four,
    					 five = five,
    					 six = six,
    					 seven = seven,									 
    					 eight = eight,
    					 nine = nine,
    					 ten = ten
    			  from   [TestData]		
    			  --This is what I would like to add in with dynamic sql
    			  --WHERE [infoField] = @FTC
    			  ) as d
    		for xml path( 'tr' ), type ) as varchar(max) )	
    			
    	set @tableHTML 
    		= '<table cellpadding="4" cellspacing="0" border="1" bordercolor="#024d6d">'
    		+ '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">table</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">one</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">two</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">three</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">four</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">five</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">six</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">seven</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">eight</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">NIne</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">Ten</font></th></tr>'
    		+ replace( replace( @tableHTML, '&lt;', '<' ), '&gt;', '>' )
    		+ '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">table</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">one</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">two</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">three</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">four</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">five</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">six</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">seven</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">eight</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">nine</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">Ten</font></th></tr>'
    		+ '<table>'	
    		
    	set @subject += convert(varchar(100),getdate(),101)
    	
    	exec msdb.dbo.sp_send_dbmail 
    		@profile_name = 'DatabaseMail', 
    		@recipients = 'test1232134@gmail.com',
    		@body_format = 'HTML',
    		@from_address = 'fromaddy1223131@gmail.com',
    		@body = @tableHTML,
    		@subject = @subject;
    		
    FETCH NEXT FROM mailcursor INTO @FTC
    
    CLOSE @FTC
    DEALLOCATE @FTC	

    Monday, September 14, 2015 7:15 PM

All replies

  • share the following result:-

    SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
           fail.mailitem_id,
           LOG.description
    FROM msdb.dbo.sysmail_event_log LOG
    join msdb.dbo.sysmail_faileditems fail
    ON fail.mailitem_id = LOG.mailitem_id
    WHERE event_type = 'error'

    --Email Sent Status
    SELECT * FROM msdb.dbo.sysmail_allitems
    SELECT * FROM msdb.dbo.sysmail_sentitems
    SELECT * FROM msdb.dbo.sysmail_unsentitems
    SELECT * FROM msdb.dbo.sysmail_faileditems


    Please click "Mark As Answer" if my post helped.

    Monday, September 14, 2015 7:42 PM
  • share the following result:-

    SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
           fail.mailitem_id,
           LOG.description
    FROM msdb.dbo.sysmail_event_log LOG
    join msdb.dbo.sysmail_faileditems fail
    ON fail.mailitem_id = LOG.mailitem_id
    WHERE event_type = 'error'

    --Email Sent Status
    SELECT * FROM msdb.dbo.sysmail_allitems
    SELECT * FROM msdb.dbo.sysmail_sentitems
    SELECT * FROM msdb.dbo.sysmail_unsentitems
    SELECT * FROM msdb.dbo.sysmail_faileditems


    Please click "Mark As Answer" if my post helped.




    Those results return over 8K rows...
    Monday, September 14, 2015 8:01 PM
  • Take note of the current time, run your process again, then run the query looking for the errors written after the time you noted
    Monday, September 14, 2015 8:10 PM
  • check for current date when you are tring to send email:-

    SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
           fail.mailitem_id,
           LOG.description
    FROM msdb.dbo.sysmail_event_log LOG
    join msdb.dbo.sysmail_faileditems fail
    ON fail.mailitem_id = LOG.mailitem_id
    WHERE event_type = 'error'

    SELECT sent_status, * FROM sysmail_allitems  --see time based


    Please click "Mark As Answer" if my post helped.

    Monday, September 14, 2015 8:39 PM
  • check for current date when you are tring to send email:-

    SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
           fail.mailitem_id,
           LOG.description
    FROM msdb.dbo.sysmail_event_log LOG
    join msdb.dbo.sysmail_faileditems fail
    ON fail.mailitem_id = LOG.mailitem_id
    WHERE event_type = 'error'

    SELECT sent_status, * FROM sysmail_allitems  --see time based


    Please click "Mark As Answer" if my post helped.

    This is the error message that I get...I XXX out the IP at the end, but it has a valid IP

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-09-14T16:50:03). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it XXX.XXX.X.XXX:XX). )

    Monday, September 14, 2015 8:56 PM
  • Monday, September 14, 2015 9:19 PM