Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2000 Text datatype

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

577 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