SP_SEND_DBMAIL @QUERY Inserts Line Breaks Into E-mail

• guerillaunit

  SSCarpal Tunnel

  Points: 4249

  More actions

  November 7, 2012 at 8:09 am

  #284392

  I use the @query perimeter in sp_send_dbmail to send out an e-mail with a list of warnings (the warnings are returned by the @query perimeter). The @query perimeter is listed as text in the e-mail. All of the records returned from the @query perimeter and displayed in the e-mail has a line break between them. ie. If I have 4 records, I would have 8 lines because of line breaks.

  How do I turn the line breaks off? I read the the msdn article for sp_send_dbmail, but it didn't mention any attributes that could be changed that would affect the line breaks.

  BEGIN

  EXEC MSDB.DBO.sp_send_dbmail

  @PROFILE_NAME = 'Alerts',

  @RECIPIENTS = @MAIL,

  @SUBJECT = @NEWSUBJECT,

  @BODY = @NEWBODY,

  @QUERY =

  'SET NOCOUNT ON

  DECLARE @HEXSTRING AS VARCHAR(100)

  SET @HEXSTRING = (SELECT HEXADECIMAL_STRING FROM mydb.dbo.statusupdates

  WHERE MACHINE_ID = ''1111'' AND DATEDIFF(MI, TIME_DATE_RECEIVED, GETDATE()) <= 60)

  SELECT [Warning_Description] FROM mydb.DBO.BINARYTOTABLE(mydb.DBO.HEXTOBINARY(@HEXSTRING)) AS ABB1

  JOIN mydb.DBO.WarningMessages ON mydb.DBO.WarningMessages.[Bit_Offset] = ABB1.BITPLACE

  WHERE BITVALUE = 1 AND ALERT_LEVEL = ''WARNING''',

  @QUERY_RESULT_HEADER = 0,

  @ATTACH_QUERY_RESULT_AS_FILE = 0;

  END