Post reply

carriage return in dynamic sql

  • February 2, 2019 at 2:45 pm

    #373737

    How do you add a carriage return with dynamic sql? This doesn't work.
    DECLARE @sql NVARCHAR(MAX)
    SET @sql=
    'SELECT FIELD1 ' + CHAR(13) + CHAR(10) + 'FROM TestTable'

    SELECT @sql

  • February 2, 2019 at 3:29 pm

    #2020933

    michaelyarbrough1975 - Saturday, February 2, 2019 2:45 PM

    How do you add a carriage return with dynamic sql? This doesn't work.
    DECLARE @sql NVARCHAR(MAX)
    SET @sql=
    'SELECT FIELD1 ' + CHAR(13) + CHAR(10) + 'FROM TestTable'

    SELECT @sql

    If you are looking at the results in SSMS grid view, you need to change it to Results To Text. It works.

    Sue

  • February 2, 2019 at 4:15 pm

    #2020934

    Do you mean change the select to print? I want to be able to copy the results in grid view and paste it in a new query window to be able to view before executing the SQL for more complex, long outputs. I want to see what is being written.

  • February 2, 2019 at 4:22 pm

    #2020935

    michaelyarbrough1975 - Saturday, February 2, 2019 4:15 PM

    Do you mean change the select to print? I want to be able to copy the results in grid view and paste it in a new query window to be able to view before executing the SQL for more complex, long outputs. I want to see what is being written.

    No changing the bottom pane where you see the results. In SQL Server Management Studio, when you run that query, go to the menu to Query and towards the end of the list is Results To where you select where you view the results. Change it from Results to Grid to Results to Text. You can view the text in that window same as you would through the grid.

    Sue

  • February 2, 2019 at 5:27 pm

    #2020936

    grid view always compresses the data to a visible sinlge row, and makes CrLf appear as spaces.
    what you want is to make sure a copy/paste from a grid still retains the CrLf, instead of converting to spaces when you copy paste.

    there is a setting in SSMS to do exactly that:
    SSMS>>Tools>>Options
    Expand Query Results>>Results to Grid: Retain CR/LF on copy or save

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 2, 2019 at 8:04 pm

    #2020940

    Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply