Solved

SQL Server 2000 Text datatype

Posted on 2007-11-26
4
2,823 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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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