Solved

SQL Server 2000 Text datatype

Posted on 2007-11-26
4
2,780 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
4 Comments
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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:
tedbilly earned 500 total points
Comment Utility
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
Comment Utility
Thanks!
0
 

Expert Comment

by:RajeevRanjanLall
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now