1

Maybe this would be very simple, but I have no idea why this is happening.

To put in simple way, I have inserted a row into a database table which contains a ntext column called content. String value which got in to this column content has a text length of 6889 characters. However when I do a basic select query based on id, it do retrieve the row - but not the complete text from the column Content. All I see in SQL Server Management Studio for this column when I do a select query is the text with characters 43679.

There is no issue with data being inserted and I could see all the text being displayed in front end application. The issue is only when I do a select query, and copy the text to notepad, I do see fewer characters. Anything with respect to SQL Server settings/paging/statistics? Appreciate if anyone could help me out with this.

thanks, KK

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Krishnakumar
  • 243
  • 1
  • 11

2 Answers2

3

I encountered similar problem, and I found that SSMS is unable to fully retrieve column that is longer than 43680 (in output to Grid mode).

So the work around is output it as XML (unlimited length):

SELECT convert(xml,'<xml><![CDATA[' + cast([your column] as varchar(max)) + ']]></xml>')

Then do a quick search and replace (&lt; to < , &gt; to > ) . You can now copy the result to Notepad++ to view all the content.

Community
  • 1
  • 1
Dio Phung
  • 5,944
  • 5
  • 37
  • 55
1

SSMS returns a maximum number of characters to the grid or text window of every text based column: SSMS menu: options: Query results node : SQL node : Results to grid / Results to text

I'm sure there is a good reason for this: probably SSMS would fall over even more regularly than it does now.

Hope this helps... no such thing as a silly question

Ian P
  • 1,724
  • 1
  • 10
  • 12