How to show "text" data type ?

• Jeff-67583

  SSCertifiable

  Points: 5625

  More actions

  November 21, 2005 at 8:55 am

  #88566

  Hi,

  MSSQL 2000/SP3

  I've ran a trace (using SQL Profiler) and a column called textdata displays only partial contents in Query Analyzer. When I go to Enterprise Manager the only thing displayed is <Long Text>. The datatype is nText size 16.

  How do I display the entire contents from this field?

   

  Many thanks. Jeff

• stephen Lawrenson

  Valued Member

  Points: 68

  More actions

  November 21, 2005 at 10:19 am

  #605003

  Hi,

   

  try selecting max(len(ntextcolumn)) and then declaring a varchar variable at that length

  Declare @ntext varchar(x)

  then select @ntext = the ntext column

   

  select @ntext = ntextcolumn from TABLE where X= XXX etc.

  you might need a convert on there though -

  select @ntext = convert(varchar(x), ntextcolumnname) from TABLE Where X = XXX

  Hope this helps

  Cheers

  Lawrenso

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 21, 2005 at 11:04 am

  #605014

  In Query Analyzer, you will be able to view up-to 8191 characters i.e. after you modify that option from (Tools/Optoins/Results/Maximum characters per column). For data more than that, you will have to do string operations to display it in its entirety.

• Jeff-67583

  SSCertifiable

  Points: 5625

  More actions

  November 21, 2005 at 11:13 am

  #605019

  Hello rsharma,

  I changed the options in QA ... great, many thanks. Could you provide me an example of what you mean by a string operation to display it's entirety?

   

  Many thanks. Jeff

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  November 21, 2005 at 11:18 pm

  #605142

  Search for "text and image functions" in books online (BOL):

  SET TEXTSIZE, UPDATETEXT, WRITETEXT, DATALENGTH, PATINDEX, READTEXT, and TEXTPTR

  Examples

  This example reads the second through twenty-sixth characters of the pr_info column in the pub_info table.

  USE pubs

  GO

  DECLARE @ptrval varbinary(16)

  SELECT @ptrval = TEXTPTR(pr_info)

     FROM pub_info pr INNER JOIN publishers p

        ON pr.pub_id = p.pub_id

        AND p.pub_name = 'New Moon Books'

  READTEXT pub_info.pr_info @ptrval 1 25

  GO

  Andy