Results 1 to 10 of 10

Thread: send mail help

  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: send mail help

    The body of my email is sending the literal query string instead of the results of my query (like I want). What do I need to update so that the body of my email is the results of my query NOT the query string itself?
    Code:
    Declare @name varchar(100),@bodytext nvarchar(max), @subject varchar(200)
    Create Table #info
    (
    	name varchar(100)
    	,itemsold varchar(100)
    )
    Insert Into #info Values
    ('ZZZZZZ', 'First'),
    ('CCCCCC', 'Last'),
    ('EEEEE', 'Green'),
    ('XXX', 'Blue'),
    ('QQQ', 'Red')
    
    DECLARE mailcursor CURSOR FOR
    SELECT DISTINCT(name) FROM #info	
    
    OPEN mailcursor
    FETCH NEXT FROM mailcursor INTO @name
    	
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    	Set @bodytext = 'Select * from #info Where name = ' +@name + ' '
    	Set @subject = 'Sent Through Code'
    	
    	exec msdb.dbo.sp_send_dbmail 
    		@profile_name = 'DatabaseMail', 
    		@recipients = 'foxtrotalphamale12343124@gmail.com',
    		@body_format = 'HTML',
    		@from_address = 'redfirebluestonesgreeneyes16@gmail.com',
    		@body = @bodytext,
    		@subject = @subject;
    				
    	FETCH NEXT FROM mailcursor INTO @name			
    
    END
    
    CLOSE mailcursor
    DEALLOCATE mailcursor

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You probably want to remove the @body parameter, and replace it with @query. Reference: sp_send_dbmail

    Code:
    exec msdb.dbo.sp_send_dbmail 
    		@profile_name = 'DatabaseMail', 
    		@recipients = 'foxtrotalphamale12343124@gmail.com',
    		@body_format = 'HTML',
    		@from_address = 'redfirebluestonesgreeneyes16@gmail.com',
    		@query = @bodytext,
    		@subject = @subject;

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Changing to @query produces the below errors:
    Code:
    Msg 22050, Level 16, State 1, Line 0
    Error formatting query, probably invalid parameters
    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504

  4. #4
    Join Date
    Feb 2012
    Posts
    188
    Upon further googling it seems that I am wanting to use the query XML functions to create a HTML document. How would I do that with dynamic SQL like above?

    How do I add dynamic SQL to this syntax?
    Code:
    set @bodytext = cast( (
    	select td = '<font color="#000000" face="verdana" size="2">' + name + '</font></td><td><font color="#000000" face="verdana" size="2">' + itemsold + '</font></td> '
    	from (
    			'Select * from #info Where name = ''' +@name + ''' '
    		  ) as d
    	for xml path( 'tr' ), type ) as varchar(max) )
    
    	set @bodytext 
    	= '<table cellpadding="4" cellspacing="0" border="1" bordercolor="#024d6d">'
    	+ '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">name</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">itemsold</font></th></tr>'
    	+ replace( replace( @tableHTML, '&lt;', '<' ), '&gt;', '>' )
    	+ '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">name</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">itemsold</font></th></tr>'
    	+ '<table>'
    Last edited by jo15765; 09-15-15 at 14:25.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The query in the @bodytext variable probably needs some quotes added:
    Code:
    Set @bodytext = 'Select * from #info Where name = ''' +@name + ''' '
    Print out a few of the @bodytext variables to see what they are producing.

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    @MCrowley you are correct @name needed 3 on each side in order for it to print properly. However, I am still unable to figure out the issue in my poast #4

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the problem you are talking about in your post #4 that the mail is being sent, but the format is pretty ugly in the HTML format?

  8. #8
    Join Date
    Feb 2012
    Posts
    188
    That gives me an error of :
    Msg 102, Level 15, State 1, Line 26
    Incorrect syntax near 'Select * from #info Where name = ''.

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    My issue appears to be attempting to use dynamic sql as if i hardcode a value into my select statement it runs fine...
    Code:
    	set @bodytext = cast( (
    	select td = '<font color="#000000" face="verdana" size="2">' + name + '</font></td><td><font color="#000000" face="verdana" size="2">' + itemsold + '</font></td> '
    	from (
    			Select * from #info Where name = 'QQQ'
    		  ) as d
    	for xml path( 'tr' ), type ) as varchar(max) )

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by The Manual
    [ @query = ] 'query'

    Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
    Since the query is executed in a separate session, the temp table is not available to it. Try either a global temp table, or a permanent table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •