Update: Microsoft will be moving away from UserVoice sites on a product-by-product basis throughout the 2021 calendar year. We will leverage 1st party solutions for customer feedback. Learn more here.

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

← SQL Server

SSMS ignores final \r\n / CRLF / Carriage Return + Line Feed in PRINT and RAISERROR

In SSMS, when using PRINT and/or RAISERROR to display messages in the "Messages" tab, if the final two characters on the line are the Carriage Return, Line Feed combination (i.e. "\r\n" or "CHAR(13) + CHAR(10)" or "CHAR(0x0D) + CHAR(0x0A)" ) then they are ignored (or "eaten") and no newline is issued as expected. BUT, if either Carriage Return OR Line Feed individually are used, then a newline is issued as expected. This is inconsistent and it should be that the Carriage Return + Line Feed combination is not ignored at the end of a line being printed.

Other forms of white-space (i.e. space and tab) are likewise not ignore. It is only Carriage Return + Line Feed, and only when used in combination.

This behavior seems to only happen in SSMS. I have also tested using SQLCMD and capturing messages in a C# application and both of those register the newline at the end of the line as expected.

I have tested with SSMS versions:
* v17.1 (14.0.17119.0)
* 2014 (12.0.5203.0)

4 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

12 comments

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    Azure Data Studio (version info below) is even worse. It also swallows trailing individual CR and LFs, hence the `REPLACE(@String, NCHAR(0x0D) COLLATE Latin1_General_100_BIN2, N'')` work around does not work there. 😾

    Version: 1.31.0 (system setup)
    Commit: a430fd2592763ef4c4a406821967e4b7b2b3d876
    Date: 2021-07-20T19:43:31.605Z
    VS Code: 1.56.0
    Electron: 12.0.7
    Chrome: 89.0.4389.128
    Node.js: 14.16.0
    V8: 8.9.255.25-electron.0
    OS: Windows_NT x64 10.0.19042

  • John M commented  ·   ·  Flag as inappropriate

    This is a pretty impactful bug given that it results in different results for different clients! Please provide an update.

    It is impacting some code of mine since it *also* applies to SELECT statements, when the two characters are added to the end of a value using CONCAT or +

  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    This is still an issue for SSMS v18.7.1 (SQL Server Management Studio - 15.0.18358.0)

    Also, @Bill Jetzer , thanks for testing via Azure Data Studio and DBeaver, and sharing the results. I did manage to come up with a couple of ways to compensate for this odd behavior, one more reliable than the other. I started writing a blog post about this but got sidetracked with something else. I hope to have it done soon, but what I can say for now is that the best / easiest approach seems to be simply replacing carriage-returns ( CHAR(13) / CR ) with nothing / empty string, leaving just the line feeds ( CHAR(10) / LF ) / newlines. For example:

    ---------------------------------------------------------------------
    DECLARE @CRLF NCHAR(2) = NCHAR(0x0D) + NCHAR(0x0A);
    DECLARE @PartA NVARCHAR(500) = N'Line1' + @CRLF +
    N'Line2 (blank line should follow)' + @CRLF + @CRLF +
    N'Line3 (blank line should follow)' + @CRLF,
    @PartB NVARCHAR(500) = N'Line4';

    PRINT @PartA;
    PRINT @PartB;

    PRINT N'------------------------';

    -- Remove carriage returns / CR / CHAR(13) / CHAR(0x0D)
    PRINT REPLACE(@PartA, NCHAR(0x0D) COLLATE Latin1_General_100_BIN2, N'');
    PRINT REPLACE(@PartB, NCHAR(0x0D) COLLATE Latin1_General_100_BIN2, N'');
    ---------------------------------------------------------------------

  • Bill Jetzer commented  ·   ·  Flag as inappropriate

    Huh. It's not even possible to write code to compensate for the behavior.

    Try something like this:

    ```
    If end of line = CR+LF then
    print everything to the left of the CR+LF
    print empty string
    else
    print the string
    ```

    And you'll realize that printing a _blank_ string actually prints a _space_! I verified that this is SQL Server behavior, not SSMS behavior, by printing a blank string in Azure Data Studio and noting the space in the output. Repro script below.

    ```
    print '----'; print ''; print rtrim(''); print left('',0); print null; print '----';
    ```

    There is a workaround for SSMS, but then you'll get extra blank lines in other tools:

    ```
    If end of line = CR+LF then
    print everything to the left of the CR+LF
    print CR+LF -- SSMS will print one blank line, all others will print 2 as expected
    else
    print the string
    ```

  • Bill Jetzer commented  ·   ·  Flag as inappropriate

    I can confirm that it seems to be peculiar to SSMS. Both Azure Data Studio and DBeaver print the expected output.

  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    This bug still exists in SSMS 18.0 Preview 6. Here is a simple test:

    ------------------------------------------------------------------
    PRINT 'A';
    PRINT 'B' + CHAR(0x0D) + CHAR(0x0A); -- NO NEWLINE in SSMS
    PRINT 'C';
    PRINT 'D' + CHAR(0x0D); -- newline is there
    PRINT 'E';
    PRINT 'F' + CHAR(0x0A); -- newline is there
    PRINT 'G';
    PRINT 'H' + CHAR(0x0D) + CHAR(0x0A) + CHAR(0x0D) + CHAR(0x0A); -- 1 newline is there, not 2
    PRINT 'I';
    ------------------------------------------------------------------

SQL Server: Bugs

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice