Copying big data from query result in MS SQL Server Management Studio

Question

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

I tried increasing the query parameters → Results → Text, but max seems to be 8192, which is not enough for me.

Any ideas on how this can be achieved?

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

TIA! Raj

Answer 1

The way I could get all the data is to use the “Save Results As ...” option and then select the TXT file and then you can open it with a good editor like notepad ++ and you will have all the data .

Cheers = 0)

Answer 2

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) --... 

Answer 3

Quick and dirty way

• Right click table - "Edit first 200 rows"
• Click Show SQL Pane
• Change SQL to return the required value
• Click Execute SQL
• Now you can copy the big result

I just copied 87K text this way.