?
Solved

SQL Server 2000 Text datatype

Posted on 2007-11-26
4
Medium Priority
?
2,831 Views
Last Modified: 2013-12-07
We have a column with a "Text" datatype in a SQL Server 2000 table.

How can I read the whole data from start to finish in a column of "Text" datatype?
It seems that while reading the data using "Select" statements it fetches only partial data.

Please help.

Thanks.
0
Comment
Question by:vdesai_8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 20354516
If using query analyzer, the maximum text data that will be returned is 8000 characters.  This is controlled in a setting (Maximum characters per column) in Tools/Options, Results tab.

To use all of the information in a text column requires either an application outside of SQL Server, or some **very** tricky coding.
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 2000 total points
ID: 20355809
The TEXT datatype is actually a pointer to a chunk of memory that is stored in seperate data pages from the rest of the data.  To access it in SQL Query Analyzer or view it in Enterprise Manager SQL has to convert it to a VARCHAR with a maximum size of 8000 bytes (1 data page minus overhead)

You can use CAST(expression AS VARCHAR(8000)) but it will truncate the TEXT.  Some people use the TEXT datatype because they think they need more than 8000 characters but actually don't.  You can use the DATALENGTH(expression) > 8000 as a WHERE clause to find columns greater than 8000 characters.
0
 

Author Closing Comment

by:vdesai_8
ID: 31411110
Thanks!
0
 

Expert Comment

by:RajeevRanjanLall
ID: 23254254
Use Management Studio of SQL Server 2005 or 2008 and you'd be able to view the complete text of Text Data Type column of your SQL Server 2000 database.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question