retrieve data from text field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

retrieve data from text field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-09 : 09:27:51

Marijn writes "I'm trying to retreive a value from a field of data type text in SQL
Server 2000.
Here's my situation :

I have a text field in my Clients table, called "Description". When I
write a stored procedure to get all the client information from 1 client
(including the Description field), I can't seem to get that Description
field. Here's in short what I do :

CREATE PROC dbo.GetClientInfo
..., @Description text OUTPUT, ...
AS
SELECT ... , @Description = [DESCRIPTION], ... FROM ...

On that SELECT Line I get this error :
Server: Msg 409, Level 16, State 1, Procedure GetClientInfoAdmin, Line 48
The assignment operator operation cannot take a text data type as an
argument.

I tried this, which works :

DECLARE @Val varbinary(16)

SELECT @Val = TEXTPTR([DESCRIPTION])
FROM [20_CLIENTS]
WHERE [ID] = '121'
READTEXT [20_CLIENTS].[DESCRIPTION] @Val 0 0

But I can't seem to put the result in READTEXT in a variable, it seems to
return some kind of recordset and the second 0 in READTEXT means that it
"only" reads 4kb bytes and my text field could contain more data.

Do you have any idea on how to solve this?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-09 : 10:07:50

Yeah, this is sort of a gripe of mine and others here on SQL Team. You cannot put data into a text datatype. Unfortunately we don't have a workaround for it yet.

Why not just use a straight SELECT statement? Don't assign the column values to variables, just use SELECT and capture the results in a recordset. This is the easiest way to do it.

If you don't need to hold more than 8000 characters, or only on rare occasions, you should use varchar instead of text. Think about this option, because 8000 characters really is A LOT of data for one row's worth. To give you some idea, this thread you are reading now will not exceed 3000 characters, and even including the HTML tags, will not exceed 8000 characters.

Edited by - robvolk on 04/09/2002 10:09:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources