How to get the content of a huge field in SQL Server?

Question

I am over SQL Server 2008 and I have a field with a huge amount of data. When I apply...

DATALENGTH(field_name)

... I get to know my field is 288.946 characters long, but on MS SQL Server Management Studio, when I try to copy-paste the result of a simple SELECT of that field into a text editor (Notepad+), it seems like SQL Server only packs 43.679 characters... Or is it that the buffer for copying text in Windows is blowing the maximum number of characters? I have tested already and the limit isn't with Notepad+ line size or something... If I copy-paste into a new query window in SQL Server, the result is the same text string limited to 43.679 characters.

Well, the question is: I need to copy-paste the whole content of that field... Is there something special I should do in my query? Thank you.

Could you try saving the results of your query directly to a file? — Chris Diver, Jul 04 '11 at 21:23
Aaron, I always accept the answers that bring a solution or at least the best available solution, and you have sent a very good answer that solves my problem. Thank you! I suggest you check the history of my posts and see what I am talking about. Sometimes I am just not sitting here after sending the question, but for sure I will be back later to profit from the great answers users in this forum provide, approve the best and vote for all the answers that also helped me. — Marcos Buarque, Jul 05 '11 at 02:05

Aaron Bertrand · Accepted Answer · 2011-07-05T00:58:36.340

I filed a bug against this during the beta of SQL Server 2008. They closed it as "fixed" but then commented that it was a duplicate of a Vista issue. Which surprised me because it appears in several other SSMS / operating system combinations as well, including Denali CTP1 on Windows 7.

http://connect.microsoft.com/SQLServer/feedback/details/344150/ssms-grid-will-not-display-43-679-characters-from-varchar-max

So, I'm not sure why they've called it fixed, because it is most certainly still a real limitation.

If you have < 64K, you can convert to XML then click on the result in grid mode. Unfortunately with more than that you're pretty limited to what SSMS is going to be able to get for you. You'll need to export to a flat file as others have suggested, use a different program to pull the data in full, or manually paste together chunks of 40000 characters. In your example you could do something like this:

DECLARE @foo TABLE (a VARCHAR(MAX));

INSERT @foo(a) SELECT REPLICATE('A', 8000);

DECLARE @i INT = 1;

WHILE @i < 36
BEGIN
    UPDATE @foo SET a += REPLICATE(CHAR(@i+64), 8000);
    SET @i += 1;
END

SELECT DATALENGTH(a), a FROM @foo;

SELECT SUBSTRING(a, 1, 40000),
    SUBSTRING(a, 40001, 40000),
    SUBSTRING(a, 80001, 40000),
    SUBSTRING(a, 120001, 40000),
    SUBSTRING(a, 160001, 40000),
    SUBSTRING(a, 200001, 40000),
    SUBSTRING(a, 240001, 40000),
    SUBSTRING(a, 280001, 40000)
FROM @foo;

But you'd have to adjust by adding more operations for columns with values > 320K.

Thank you so much for the detailed description! Best regards. — Marcos Buarque, Jul 05 '11 at 02:00
How about: `DECLARE @txt varchar(max) SET @txt = (SELECT TOP 1 someText from tblTest) print @txt` — Magnus, Jul 05 '11 at 08:47
Magnus, did you try this? PRINT has a built-in limitation of 8000 characters, so simply PRINTing the result is not going to work, for much the same reason that simply SELECTing the result is not going to work. — Aaron Bertrand, Jul 05 '11 at 16:34

score 2 · Answer 2 · edited Jan 07 '15 at 02:51

2

If you add FOR XML AUTO to your query, and run it with results to Grid, you'll get an XML result that includes the entire contents of the TEXT field.

e.g.

SELECT large_field FROM MyTable --Limited to 65535 characters in Grid
SELECT large_field FROM MyTable FOR XML AUTO --Limited to 2G characters in Grid in XML format

You can then copy the contents from the XML editor window in SSMS and strip off the XML from around the results you are interested in.

edited Jan 07 '15 at 02:51

Nerdroid

13,398
5
58
69

answered Jan 07 '15 at 02:35

SQLGuy

41
4

1

This is painful if the content of the column is XML. – gravidThoughts Jul 12 '16 at 18:38

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

As shown here you can install the plugin SSMSBoost to copy the text

edited May 23 '17 at 12:08

Community

1
1

answered Feb 05 '14 at 08:18

Thomas

2,137
1
17
38

How to get the content of a huge field in SQL Server?

3 Answers3