Dynamic parameter transfer in msdb.dbo.sp_send_dbmail

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  March 30, 2021 at 3:27 pm

  #3863651

  So now you want a column named 'User' with the value 'Total Minutes' - where before it was 'Report' and in your original code is was the user name.  I showed how to make the change to the code to list by 'Report' and give the totals - just change that value to 'Total Minutes'.

  Please provide sample data in the form of a create/declare table - and insert statement to populate the sample data.  Then - the expected results from that sample data.  The sample data I provided and the results being returned meet your requirements - as far as I can tell without any sample data.

  To adjust the color and style of the table - you can modify the embedded CSS style sheet:

   --==== Setup the CSS style of the message
      Set @body = '<style type=''text/css''>';
      Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
      Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
      Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
      Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
      Set @body += '</style>'
  

  The style sheets defines the values - so for <td> (table data) the font will be 10pt, the text alignment will be centered, the border will be 1px, color will be DarkCyan and solid.  Padding in the cell will be 2px on each side (top, right, bottom, left).

  Each <tr> has the color Black - with background-color DarkCyan.  Change DarkCyan to the background color you want.  To get white background and black text:

   --==== Setup the CSS style of the message
      Set @body = '<style type=''text/css''>';
      Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
      Set @body += 'td {font-size:10pt; text-align:center; border:1px Black solid; padding:2px 2px 2px 2px;}';
      Set @body += 'th {font-size:10pt; text-align:center; border:1px Black solid; padding:2px 2px 2px 2px;}'; -- background-color:White; color:Black;}';
      Set @body += 'name tr {color:Black; background-color:White;}';
      Set @body += '</style>'
  

  This results in the following:

  

  Adjust the CSS style as needed - add/removing style parameters.

   

  You can use this style sheet:

   --==== Setup the CSS style of the message
      Set @body = '<style type=''text/css''>';
      Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%;}';
      Set @body += 'table, th, td {font-size:10pt; text-align:center; border:thin solid; padding:2px 10px 2px 10px; border-collapse:collapse;}';
      Set @body += 'name tr {color:Black; background-color:White;}';
      Set @body += '</style>'
  

  And here are the results:

  

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs