SQL SERVER – Increase Characters Displayed in Text Results

Working with various SQL Server options is always a great feeling. Every now and then I go to the Options page to get just the thing I want. When I wrote the blog post – SQL SERVER – Getting started and creating JSON using SQL Server 2016 I was expecting interesting conversations about JSON use. On the contrary, guess what – I got an email from a junior explorer of SQL Server 2016 CTP is stating that he was not getting the same output that I had shown in my blog post. I was surprised by this statement.

I asked the mailer to send me a screen shot or an example of what he was seeing. Lesser did I know the context because this was exactly what I had got and I had changed something for the blog post.

Using Query Options

Here are the steps I did while I was writing the previous blog post.

Right click in the query editor and select Query Options. Under Results, select Text. Uncheck “Include column headers in the result set” and change the maximum number of characters displayed to 8192.

Click on in the editor and click Results To and choose Results to Text and click OK.

SQL SERVER - Increase Characters Displayed in Text Results increase-max-number-character-01

After making the above change, I was able to get upto 8192 characters as part of output to Text.

This is one the simplest option that I thought was easy to find and yet had to share them with couple of readers. Since I was repeating the same, I thought it was worth to write it as a blog post for you.

Note: The default maximum number of characters displayed in each column is 256. The maximum allowed value is 8192.

Do let me know if you have ever changed this option every in your environments? What value did you use whenever you had to change? Let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Interview Question of the Week #030 – Retrieve Last Inserted Identity of Record
Next Post
SQL SERVER – Unable to Start SQL – Error: SQL Server Could Not Spawn Lazy Writer Thread

Related Posts

2 Comments. Leave new

  • How would you get over 8192 chars?

    Reply
  • asushilushil Agarwal Bansal
    January 9, 2020 11:58 am

    sorry sir, may be i am wrong, but changing max result to text to 8192 did not show 2257 length json it was shwoing truncated json output column, i was forced to store json to a nvarchar(max) and then get it completly. can you guess what i would have done wrong ?

    Reply

Leave a Reply

Menu