SQL was working fine last week with the old CHAR(13)+CHAR(10) for line feed/carriage return.

DECLARE @text varchar(2000)
SET @text = 
'Attached is your new reporting ID and temporary password.'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'The new login/password will be updated  on ' + Convert(char(10), @ticketdate,101)

Last week this ran as expected giving me nicely formatted text with a space between the lines.

This week, the same code on the same server is returning one long run-on line of text.

This seems like this is a collation issue? Or something like that?

The code has worked as expect so something changed, but I can't identify what might have changed.

This seems like it could be collation issue? But it appears to be the default latin setting.

  • How are you outputting the results? Grid view (CTRL + D) or Text-based (CTRL + T)? – John Eisbrener Oct 4 '17 at 18:08
  • I've been outputting it to grid. The text and file options still recognize the carriage returns, but the text gets cut off, which is why I was sending to grid. – user238855 Oct 4 '17 at 18:11
  • It seems to be a server issue. I changed servers and this works on sql server 2012 as expected. But sql server 2016 (with 2017 interface), this carriage returns are ignored. Just a run on with some extra spaces. – user238855 Oct 4 '17 at 18:19
  • Did you try outputting the results from the "faulty" server into Text-View to see if the newline characters come across? I posted an answer, but if they're not showing up in the Text-View on this particular server it's likely a different issue... – John Eisbrener Oct 4 '17 at 18:20
  • Are you positive it's the database and not the webserver (IIS)? I'm seeing an issue where IIS is stripping out newlines from my responses in server 2016.. – Amalgovinus Nov 27 '18 at 1:18

Since you're outputting to Grid View, I suspect that the option to Retain CR/LF on copy or save is disabled. Enable this option, open a new query window, run the same query, and your newline characters should now come across after you paste results out of a Grid View. In SSMS 2017, the Setting can be found under ToolsOptionsQuery ResultsSQL ServerResults to Grid. The path is in a similar location in other versions of SSMS.

enter image description here

Alternatively, if you want to apply this setting to the immediate query window only, enable the Retain CR/LF on copy or save option which can be found under QueryQuery Options...ResultsGrid. These settings won't be saved across all query windows, rather they are only applied to the current one.

| improve this answer | |
  • Thanks a lot. It was so irritating to view the result without line break. – Falaque Dec 21 '17 at 6:21
  • SSMS requires reboot (closing of application) and opening again, and then changes will be reflected – user169581 Jan 8 '19 at 9:28

You dont have to open a new query window, you can right click the query, select Query Options and go to Results > Grid and the same option is there.

Changing it in the above would update the default settings for a query window (so depends if you always want it that way or just for this instance).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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