Post reply

Adjusting column wiidths; SP_SEND_DBMAIL

  • November 18, 2013 at 8:08 am

    #285356

    Hello All,

    I am working on a small project using SP_SEND_DBMAIL. The question I have is, how do you you set the the output column width for the query results? Information from MS shows it defaults to int 256, and that it can be adjusted to between 10 and 327767, using the @query_results_width parameter.

    Can this parameter be applied to each individual column? If so, could someone share some examples of how it is used (or any other process you might use)

    Here is an example of one of my current SP's:

    EXEC msdb.dbo.sp_send_dbmail

    @PROFILE_NAME = 'ENTSMTP',

    @RECIPIENTS = 'KEITH.E.STERN@ANYCOMPANY.COM',

    @QUERY = 'SELECT [FACILITY NAME],[FAILURE],[CALL TRACKING NUMBER],[LANE],[DATE OPEN],[TIME OPEN],[NOTES]

    FROM [EQUIPMENT].[DBO].[Event Log]

    WHERE [Intial Up/Down] = ''DOWN'' AND [UP/DOWN] IS NULL',

    @BODY = 'The following facility(ies) have lanes down',

    @SUBJECT = 'Lane Down Repair Request';Regards!

    Keith

  • November 18, 2013 at 10:27 am

    #1667200

    I would use HTML to output your results. Looks much better when sending in email too.

    In fact I was doing something like this last Friday and here is the code I used to send the email:

    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =

    N'<font face ="verdana" size="2">The are several B2B files in the source folder. Unable to determine which file to load!</font>

    ' +

    N'<style type="text/css">' +

    N'.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #729ea5;border-collapse: collapse;}' +

    N'.tftable th {font-size:12px;background-color:#acc8cc;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;text-align:left;}' +

    N'.tftable tr {background-color:#ffffff;}' +

    N'.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;}' +

    N'.tftable tr:hover {background-color:#ffff99;}</style>' +

    N'<table class="tftable" border="1">' +

    N'<tr><th>File Name</th></tr>' +

    CAST ( ( SELECT td = SubDirectory

    FROM FilesToProcess

    WHERE IsFile = 1

    AND SubDirectory <> 'GetDBSDataFromSFTP.txt'

    AND SubDirectory like '%B2B%'

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SSIS',

    @recipients = 'AbuDina@ABC.com',

    @subject = 'Too many B2B files in source folder!',

    @body = @tableHTML,

    @body_format = 'HTML';

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • November 19, 2013 at 8:19 pm

    #1667653

    I will give that a try.

    Thanks!

    Keith

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply