title: SQL Server 2014 takes off leading zeroes when making Excel file. . . but.
body: <p>This <code>sp_send_dbmail</code> script works in one of our processes. It attaches an Excel file filled with whatever the query is. It knows to do this because of the extension on the file's name (.xls). </p>
  
  <p>However, it changes a <code>varchar(50)</code> field into a number field, and removes the leading zeroes. This is a known annoyance dealt with in a million ways that won't work for my process.</p>
  
  <pre><code>EXEC msdb.dbo.sp_send_dbmail 
      @profile_name = @profileName
     ,@recipients = @emailRecipientList
     ,@subject = @subject
     ,@importance = @importance
     ,@body = @emailMsg
     ,@body_format = 'html'
     ,@query = @QuerySQL
     ,@execute_query_database = @QueryDB
     ,@attach_query_result_as_file = 1
     ,@query_attachment_filename = @QueryExcelFileName
     ,@query_result_header = 1
     ,@query_result_width = @QueryWidth
     ,@query_result_separator = @QuerySep
     ,@query_result_no_padding = 1
  </code></pre>
  
  <p>Examples of problem below: this simple query changes the StringNumber column from varchar to number in Excel, and removes the zeroes.</p>
  
  <pre><code>SELECT [RowID],[Verbage], StringNumber FROM [dbo].[tblTestStringNumber]
  </code></pre>
  
  <p>In SQL Server (desired format):</p>
  
  <p><a href="https://i.stack.imgur.com/cEsIp.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cEsIp.jpg" alt="View of table in SQL Server"></a></p>
  
  <p>After in Excel (leading zeroes missing):</p>
  
  <p><a href="https://i.stack.imgur.com/JYP1P.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JYP1P.jpg" alt="Excel results with no leading zeroes"></a></p>
  
  <p>Now, there might be a way. I only say this because in SQL Server 2016 results pane, if you right click in upper left hand corner, it gives the option of "Open in Excel"</p>
  
  <p><a href="https://i.stack.imgur.com/si4Oj.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/si4Oj.jpg" alt="Right Click to Open in Excel"></a></p>
  
  <p>And. . . . drum roll . . . the dataset opens in Excel and the leading zeroes are still there!</p>
  
  <p><a href="https://i.stack.imgur.com/I84zy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/I84zy.jpg" alt="enter image description here"></a></p>