title: sp_send_dbmail not sending multiple rows from the query in the message
body: <p>I am switching some code from xp_sendmail, but am not getting the results I expect from sp_send_dbmail.  The query I am trying is below, and the message body in the email only contains one line: </p>
  
  <p>My first header entry</p>
  
  <p>Did Microsoft really design this to only return answers from single row queries (e.g. COUNT(*) or TOP 1)?  Is there a way to get the entire set of rows as the text?</p>
  
  <pre><code>DECLARE @tLog TABLE (Sequence INT IDENTITY(1,1), [Rank] TINYINT DEFAULT 1, Line VARCHAR(8000))
  INSERT INTO @tLog ([Line]) VALUES ('My first log entry')
  INSERT INTO @tLog ([Line]) VALUES ('My first second entry')
  /* ... processing ... */
  INSERT INTO @tLog ([Rank], [Line]) VALUES (0, 'My first header entry')
  /* Global Temp for visibility in sp_send_dbmail */
  SELECT Sequence, [Rank], Line 
  INTO ##tResult1
  FROM @tLog 
  ORDER BY [Rank], Sequence
  /* to validate table content only */
  SELECT Sequence, [Rank], Line 
  FROM ##tResult1
  ORDER BY [Rank], Sequence
  EXEC msdb.dbo.sp_send_dbmail 
      @profile_name = 'MyProfile',
      @recipients='me@myaddress.net',
      @subject = 'test',
      @body_format = 'text',
      @body = '',
      @query_result_header = 0,
      @query_no_truncate = 1,
      @query = 'SELECT Line FROM ##tResult1 ORDER BY [Rank], Sequence'
  
  DROP TABLE ##tResult1   
  </code></pre>