Remove Spaces Between Columns In Results To Text

• theunique1011

  Right there with Babe

  Points: 795

  More actions

  March 8, 2011 at 9:34 am

  #247764

  Hi

  I'm trying to send results as email using the following command

  EXEC MASTER.DBO.XP_SENDMAIL

  @Recipients=N'xxxxxxxx@xxx.com'

  @Query=N'select*from sys.sysdatabases'

  how to Remove Space Between Columns In Results

  When you set the results from a query to text, very often you end up with a format like:

  job_id name

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

  Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns

  Thanks!

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  March 8, 2011 at 9:48 am

  #1295981

  That looks like the columns are fixed length. Try looking at rtrim function.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  March 8, 2011 at 11:13 am

  #1296028

  What you are seeing is the same as what you would see if you ran the query in SSMS with output set to Text...even varchar columns are output as fixed width.

  I would recommend switching from xp_sendmail to Database Mail and using sp_send_dbmail to send email from SQL Server.

  sp_send_dbmail offers many options for formatting query output...maybe an attachment would work better for you. Using a comma for a query separator and a file name with a .csv extension will create an attachment that can automatically open in Excel if the recipient has Excel installed and left alone the default file associations.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato