110

In this live SQL Server 2008 (build 10.0.1600) database, there's an Events table, which contains a text column named Details. (Yes, I realize this should actually be a varchar(MAX) column, but whoever set this database up did not do it that way.)

This column contains very large logs of exceptions and associated JSON data that I'm trying to access through SQL Server Management Studio, but whenever I copy the results from the grid to a text editor, it truncates it at 43679 characters.

I've read on various locations on the Internet that you can set your Maximum Characters Retrieved for XML Data in Tools > Options > Query Results > SQL Server > Results To Grid to Unlimited, and then perform a query such as this:

select Convert(xml, Details) from Events
where EventID = 13920

(Note that the data is column is not XML at all. CONVERTing the column to XML is merely a workaround I found from Googling that someone else has used to get around the limit SSMS has from retrieving data from a text or varchar(MAX) column.)

However, after setting the option above, running the query, and clicking on the link in the result, I still get the following error:

Unable to show XML. The following error happened: Unexpected end of file has occurred. Line 5, position 220160.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

So, any idea on how to access this data? Would converting the column to varchar(MAX) fix my woes?

adamjford
  • 7,478
  • 6
  • 29
  • 41

11 Answers11

111

SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options.

enter image description here

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

In SSMS (at least versions 2012 to current of 18.3) this displays the results as below

enter image description here

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

A more robust way of doing this that avoids issues of SQL Server converting < to &lt; etc or failing due to these characters is below (credit Adam Machanic here).

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 2
    Adding CDATA to the Convert statement worked! Thanks a lot. :) – adamjford May 03 '10 at 19:15
  • Adding `CDATA` may work, but if your data includes control characters, you have to perform a replace operation. In my case, I was using the Unit Separator, ASCII Code 31, within my data. Since I was only using that one character in many places, a simple `REPLACE(details, char(31), '&x1f;')` sufficed. If I had unknown characters or a large number of different characters to replace, I may have had to find another solution. – Zarepheth Jan 07 '14 at 23:42
  • @Zarepheth - The `CDATA` thing was my first suggestion. The later one of `AS [processing-instruction(x)]` avoids that. – Martin Smith Jan 07 '14 at 23:44
  • 2
    @MartinSmith excellent answer ! Wonder how much I learn from your answers ! +1 – Kin Shah Jul 07 '16 at 17:29
  • The SSMS setting worked! I did need to restart SSMS (18.9.2) for the change to take in effect. – Jan_V Oct 04 '21 at 12:11
67

I was able to get this to work...

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;
Scott Durbin
  • 679
  • 5
  • 2
37

One work-around is to right-click on the result set and select "Save Results As...". This exports it to a CSV file with the entire contents of the column. Not perfect but worked well enough for me.

Workaround

Oleg Fridman
  • 1,560
  • 16
  • 16
19

Did you try this simple solution? Only 2 clicks away!

At the query window,

  1. set query options to "Results to Grid", run your query
  2. Right click on the results tab at the grid corner, save results as any files

You will get all the text you want to see in the file!!! I can see 130,556 characters for my result of a varchar(MAX) field

Results in a file

Jenna Leaf
  • 2,255
  • 21
  • 29
5

The simplest workaround I found is to backup the table and view the script. To do this

  1. Right click your database and choose Tasks > Generate Scripts...
  2. "Introduction" page click Next
  3. "Choose Objects" page
    1. Choose the Select specific database objects and select your table.
    2. Click Next
  4. "Set Scripting Options" page
    1. Set the output type to Save scripts to a specific location
    2. Select Save to file and fill in the related options
    3. Click the Advanced button
    4. Set General > Types of data to script to Data only or Schema and Data and click ok
    5. Click Next
  5. "Summary Page" click next
  6. Your sql script should be generated based on the options you set in 4.2. Open this file up and view your data.
Kevin Brydon
  • 12,524
  • 8
  • 46
  • 76
3

It sounds like the Xml may not be well formed. If that is the case, then you will not be able to cast it as Xml and given that, you are limited in how much text you can return in Management Studio. However, you could break up the text into smaller chunks like so:

With Tally As
    (
        Select ROW_NUMBER() OVER ( ORDER BY s1.object_id ) - 1 As Num
        From sys.sysobjects As s1
            Cross Join sys.sysobjects As s2
    )
Select Substring(T1.textCol, T2.Num * 8000 + 1, 8000)
From Table As T1
    Cross Join Tally As T2
Where T2.Num <= Ceiling(Len(T1.textCol) / 8000)
Order By T2.Num

You would then need to manually combine them again.

EDIT

It sounds like there are some characters in the text data that the Xml parser does not like. You could try converting those values to entities and then try the Convert(xml, data) trick. So something like:

Update Table
Set Data = Replace(Cast(Data As varchar(max)),'<','&lt;')

(I needed to cast to varchar(max) because the replace function will not work on text columns. There should not be any reason you couldn't convert those text columns to varchar(max).)

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • Oh, I suppose I should mention that the column is not XML at all. CONVERTing the column to XML is merely a workaround I found from Googling that someone else has used to get around the limit SSMS has from retrieving data from a `text` or `varchar(MAX)` column. – adamjford May 03 '10 at 17:24
  • 1
    EDIT of your code above;With Tally As ( Select ROW_NUMBER() OVER ( ORDER BY s1.id ) - 1 As Num From sys.sysobjects As s1 Cross Join sys.sysobjects As s2 ) Select Substring(T1.YourTextCol, T2.Num * 8000 + 1, 8000) From YourTable As T1 Cross Join Tally As T2 Where T2.Num <= Ceiling(datalength(T1.YourTextCol) / 8000) Order By T2.Num – Ian Quigley Apr 05 '12 at 09:02
  • Using the `TSQL` from @IanQuigley based on this answer worked well for me. I basically took the results (ended up being 11 records) and just pasted them together in Notepad. Worked perfectly. Need to save this script. I had some crazy long XML that included invalid characters. – atconway Jul 31 '13 at 16:00
3

The data type TEXT is old and should not be used anymore, it is a pain to select data out of a TEXT column.

ntext, text, and image (Transact-SQL)

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

you need to use TEXTPTR (Transact-SQL) to retrieve the text data.

Also see this article on Handling The Text Data Type.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • Will converting the column to `varchar(MAX)` prevent SSMS from truncating the data from it? – adamjford May 03 '10 at 17:23
  • I would first use SSMS to generate the scrip to convert your TEXT column to varchar(max). From there you could evaluate if you want to further convert it to an XML column. As for SSMS display limits, results to grid can be set to display up to 65,535 chars of non xml data per column, and up to an unlimited amount of xml data (fyi, table columns of XML type can only store 2 GB). While results to text is limited to 8192 chars per column. To set these default max limits, just go to (in SSMS) TOOLS menu, then OPTIONS... then QUERY RESULTS, then SQL SERVER, then RESULTS TO GRID or RESULTS TO TEXT. – KM. May 03 '10 at 17:39
1

You are out of luck, I think. THe problem is not a SQL level problem as all other answers seem to focus on, but simply one of the user interface. Management Studio is not meant to be a general purpose / generic data access interface. It is not there to be your interface, but your administrative area, and it has serious limitations handling binary data and large test data - because people using it within the specified usage profile will not run into this problem.

Presenting large text data is simply not the planned usage.

Your only choice would be a table valued function that takes the text input and cuts it rows for every line, so that Management Studio gets a list of rows, not a single row.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • I feel like this answer is guesswork at best, or do you have any sources for the "planned usage"? If it's an SQL "management studio" it should at the very least be able to show me the content of my databases. Absolutely awful UI. – damd Jan 03 '17 at 14:00
  • This answer should be deleted as it is no longer applicable – Caius Jard Apr 23 '20 at 12:19
1

I prefer this simple XML hack which makes columns clickable in SSMS on a cell-by-cell basis. With this method, you can view your data quickly in SSMS’s tabular view and click on particular cells to see the full value when they are interesting. This is identical to the OP’s technique except that it avoids the XML errors.

SELECT
     e.EventID
    ,CAST(REPLACE(REPLACE(e.Details, '&', '&amp;'), '<', '&lt;') AS XML) Details
FROM Events e
WHERE 1=1
AND e.EventID BETWEEN 13920 AND 13930
;
binki
  • 7,754
  • 5
  • 64
  • 110
  • Note that some characters are invalid in XML 1.0 but valid in `NVARCHAR`/`VARCHAR`. For example, the NUL character (different from `NULL` as a value for the field). The cast will fail for strings with such embedded values. – binki Jul 07 '20 at 18:25
1

Starting from SSMS 18.2, you can now view up to 2 million characters in the grid results. Source

Allow more data to be displayed (Result to Text) and stored in cells (Result to Grid). SSMS now allows up to 2M characters for both.

I verified this with the code below.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,2000000) + 'B' 

SELECT @S as a
Gabe
  • 5,113
  • 11
  • 55
  • 88
0
declare @takeOver table(details nvarchar(max))
declare @json_auto nvarchar(max)

select @json_auto = (select distinct 
From table_1 cg
inner join table_2 c
on cg.column_1= c.column_1and cg.isDeleted =0 and c.isdeleted = 0
inner join table_3 d
on c.column_2= d.column_2 and d.isdeleted = 0
where cg.Id= 1017
for Json Auto)

insert into @takeOver
values(@json_auto)

select * from @takeOver
vimuth
  • 5,064
  • 33
  • 79
  • 116
DarkDemons
  • 1
  • 1