When changing to type text from nvarchar, the 255 character limit is maintained?

[iporter@intraspin.com]

Hi - I am changing a field from type nvarchar to type text, given that
I need to store strings longer than 255 characters. To do this I
change the data type in SQL Server, then I change the parameter code in
the calling procedure, as per below:

cmd.Parameters. Append(cmd.Crea teParameter("@t itle", adVarWChar,
adParamInput, 255, title));

becomes:

cmd.Parameters. Append(cmd.Crea teParameter("@t itle", adLongVarWChar,
adParamInput, 1073741823, title));


However, when I do this, for some reason, the field is still limited to
255 characters - when I try to update the field with 256 characters,
the error 'Application uses a value of the wrong type for the current
operation.' occurs.

Why is this? I've checked that the correct data is contained in the
parameter. When I look at the data in the database, the column in
question shows the content, whereas the next column, which has always
been of type text, shows '<LongText>' - does this mean anything? Do I
need to do something special to convert the column from nvarchar to
text?

Many thanks,
Iain

[Erland Sommarskog]

Re: When changing to type text from nvarchar, the 255 character limit is maintained?

(iporter@intras pin.com) writes:[color=blue]
> Hi - I am changing a field from type nvarchar to type text, given that
> I need to store strings longer than 255 characters. To do this I
> change the data type in SQL Server, then I change the parameter code in
> the calling procedure, as per below:[/color]

Well, nvarchar can be up to 4000 characters long, so there is no need
to go to text directly.

And if you are using nvarchar because you need to support Unicode, you
should use ntext, not text.
[color=blue]
> However, when I do this, for some reason, the field is still limited to
> 255 characters - when I try to update the field with 256 characters,
> the error 'Application uses a value of the wrong type for the current
> operation.' occurs.
>
> Why is this? I've checked that the correct data is contained in the
> parameter. When I look at the data in the database, the column in
> question shows the content, whereas the next column, which has always
> been of type text, shows '<LongText>' - does this mean anything? Do I
> need to do something special to convert the column from nvarchar to
> text?[/color]

Judging from what you see in Enterprise Manager, it appears that your
column is still nvarchar(255). You can verify this by running
sp_help on the table in Query Analyzer.

I don't know what could have happened, but I get the feeling that
you used the Table Designer in Enterprise Manager. This tool has
several serious flaws. Run an ALTER TABLE ALTER COLUMN from Query
Analyzer instead.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp