Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP_SEND_DBMAIL @QUERY Inserts Line Breaks Into E-mail Expand / Collapse
Author
Message
Posted Wednesday, November 07, 2012 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329
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

Post #1382006
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse