Select all of an NVARCHAR(MAX) column's contents

[RRinTetons]

I want to get the entire content of an NVARCHAR(MAX) column in a single row out into a text file for inspection. If I set query results to go to a file and write:

SELECT m.MyCol
FROM MyTable m
WHERE m.ID = 1

I get one row, but only the first 255 characters. LEN(m.MyCol) tells me it's 5997 chars long, and I can pick out 255 char substrings, but I want to inspect the whole thing.

What am I missing?

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Sorry, make that 59,997 characters...

-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

Go to Tools\Options\Query Results\Results to Text and set Maximum number of characters displayed in each column to some big number (mine is set to 6000).

PluralSight Learning Library

 

[RRinTetons]

Hmmm. The biggest it will take is 8,192, and that's a long way from 60,000. Even when set that I'm still only getting 255 returned for an NVARCHAR(MAX).

SQL Server 2008R2, btw.

-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

I set it to 6000, not 60000.

You can also use bcp to generate the result. I believe in this case it will output everything.

PluralSight Learning Library

 

[RRinTetons]

BCP worked, I was able to get the entire content out and study it to find out what I need. Onwards in XQuery land.

-
Richard Ray
Jackson Hole Mountain Resort