sp_send_dbmail @query fails when there is only one row of data.

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  September 19, 2018 at 5:48 am

  #379191

  I think I might be going mad, certainly boz eyed.  Grateful for a second pair of eyes & brain cells.

  So I have a small script that emails out the results of a query.  I can't cut & paste easily between systems into this window for boring reasons.  So the gist is as follows:

  Create global temp table
  Insert values
  sp_send_dbmail @query = 'SELECT * FROM global temp table'
  Drop global temp table.

  This works, as in the mail appears in my inbox, when I have > 1 row in my temp table.
  It fails when there is only 1 row - (fails as in says "Mail queued" but the mail never appears).

  This is the only difference between success and failure:
  INSERT ##a VALUES (1), (2) -- mail appears
  INSERT ##a VALUES (1) -- mail doesn't appear.

  Is this a known issues / something I'm doing wrong?

  SQL 2008 R2S

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  September 19, 2018 at 6:19 am

  #2006250

  So there are workarounds: it works fine if I go through the whole format as HTML business.  Also if I set @attach_query_result_as_file = 1 (instead of zero).  But it's really annoying.