How to get complete data from SQL management studio for ntext column?
I am using SQL server 2005. In one of the tables, I have a column "xmldefinition" which is of ntext type. Now the data in this column is very huge and contains whole xml text.
eg:- <root><something1>....</something1></root>
I want to get the whole string from management studio and copy it outside in a xml file just to go through the whole xml manually. But when I query for this column and I copy and paste the data into another file, the contents are broken in middle and it is not complete.
eg:- <root><something1>........<somechar
I believe this will copy only some 8196 characters from xml data in column. So my question is, how do I get the complete data for this column manually. I can however write a C# code to read that column, but I want to do this manually in management studio. Any idea please.
Answer
The export technique shown in SQL Server truncation and 8192 limitation worked for me. In summary it says:
You can export the data to a flat file which will not be truncated. To do this:
- Right click the Database
- Click Tasks -> Export Data
- Select your Data Source (defaults should be fine)
- Choose "Flat File Destination" for the Destination type.
- Pick a file name for the output.
- On the "Specify Table Copy or Query", choose "Write a query to specify the data to transfer"
- Paste in your query
Remaining steps should be self explanatory. This will output the file to text and you can open it in your favorite text editor.