Hi All
I have a bug where a text field is saving text to a limit of 256 characters,
which is no where near enough.  The table structure is an ID with an
identity Int, and a field called responseText.

I cannot run an alter table as "alter table tempresponses alter column
responsetext varchar(5000)"
Because "Cannot alter column 'responsetext' because it is 'text'."

I have tried to add a temporary column, but this makes it worse:
Alter table tempresponses add temp_column varchar(5000)   ----Add temp
column
update tempresponses set temp_column = cast(ResponseText as varchar)   -- 
Fill out.  Size irrelevant as is max 256 currently
alter table tempresponses drop column ResponseText  -- Drop the TEXT
alter table tempresponses add ResponseText varchar (5000)  --- read as
varchar
update tempresponses set ResponseText=temp_column
alter table tempresponses drop column temp_column

But this doesn't seem to work because it promptly crops the data at 30 chars
on doing the cast.  I cannot see why.   I got this solution from the
Microsoft Online Books.

Is there anything else I can try?

I have two tables that are doing this currently, and people are going to be
losing data if I cant figure this out.  Shutting the database down is about
my only option at this stage.

Help.

Many Thanks
Ashley

Are you sure that is all that is being saved?  Or are you relying on Query
Analyzer in its default configuration and counting the characters that it
returns?  Try SELECT DATALENGTH(column) to see if the length really is
255/256.

This is a very common symptom and *almost always* it is a mis-diagnosis
because of http://www.aspfaq.com/2272.

If the data really isn't getting into the table, then it is usually either a
stored procedure @param declaration issue, where the defined size is
VARCHAR(x), or a problem in the application that is generating the call to
do the insert.

Yeah - the stored procedure that initially padded the table was varchar so I
fixed that instead.   I did wonder for a while if it was Query Analyser.
The mis-diagnosis info was very useful and I have put into a FAQ db that I
use for my own references.   Sweet, thanks


Show quoteHide quote

(snip)

Hi Ashley,

Aaron already replied to your main question, but he didn't comment on
this part:

If varchar (or char) is specified without length, it will in most cases
default to a length of 1 character. The only exception is in a CAST
function: for reasons beyond me, the default is set to 30 characters in
this context.

That's why the statement above crops your data to 30 characters.

Best, Hugo

Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key
boolean programming
JDBC client - CURSOR as OUT parameter -supported ?
Creating a trigger using a cursor