[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Server 2000 Text datatype

Posted on 2007-11-26
4
Medium Priority
?
2,838 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

650 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