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

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 chara开发者_C百科cters? 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.

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.

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.

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