0

Query below has fixed length values for data set being returned in a string, now the problem is that fixed lengths are not being honored in the query results. But the output text file produced by DB mail looks correct. See examples below:

     SELECT 
          LEFT(CONVERT(char(9), machine) + SPACE(9),9) 
          + LEFT(CONVERT(char(30), part_no) + SPACE(30), 30) 
          + LEFT(CONVERT(char(7), ordered) + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), picked)  + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), allocated)  + SPACE(7), 7)
          + LEFT(CONVERT(char(7), in_stock)  + SPACE(7), 7) 
          + LEFT(CONVERT(char(7), sh_bin_qty)  + SPACE(7), 7)  
          + LEFT(CONVERT(char(7), short)  + SPACE(7), 7) 
          + min_bin

Raw Output Results

image1

Text File Results

image1

1 Answer 1

4

It is honoring the fixed character length. However, it does not appear to be using a monospaced font so it looks like it isn't. The character length is fixed for the column. The character width is font dependent. DB Mail is using a monospaced font.

https://en.wikipedia.org/wiki/Monospaced_font

A monospaced font, also called a fixed-pitch, fixed-width, or non-proportional font, is a font whose letters and characters each occupy the same amount of horizontal space.[1] This contrasts with variable-width fonts, where the letters and spacings have different widths.

Notice that the space taken up by an M is more than taken up by an I

2
  • If I want to send the results in an email body instead of an attachment would I have to use an HTML table to have the correct format? If I set @attach_query_result_as_file = 0 i receive the non monospace output.
    – tfenwick11
    Commented Sep 15, 2017 at 11:11
  • As long as the recipient isn't limited to plain text emails that would work. You could also attach the data as a spreadsheet.
    – indiri
    Commented Sep 15, 2017 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.