Copying large data from result of query in MS SQL Server Management Studio

Question

Hello, I have a query that returns a large 'ntext' result. I want to copy this over to a plain text editor (Notepad), but only a part gets copied over.

I tried increasing Query Options -> Results -> Text, but the max seems 8192, which is insufficient for me.

Any ideas on how this can be achieved?

I'm using SQL Server Management Studio 2008, if that matters.

TIA! Raj

Answer 1

try something like this:

--creates file on server
declare @cmd varchar(1000)
select @cmd = 'osql -U -P -S -Q"select * from yourtable" -o"c:\yourtextfile.txt" -w50000'
exec master..xp_cmdshell @cmd

or

--creates file on server
master..xp_cmdshell 'bcp your_table_or_view out c:\file.bcp -S -U -P -c '

or

--the limit of 8192 is per column, so split your column into multiple columns
--you will get a 1 character gap between these "columns" though
;WITH YourQuery AS
(
    SELECT
        col1
        FROM ...
)
SELECT SUBSTRING(col1,1,8192), SUBSTRING(col1,8193,8192), SUBSTRING(col1,16385,8192) --...