[dba-SQLServer] Convert from ntext to varchar

Mackin, Christopher CMackin at quiznos.com
Thu Jan 6 13:22:51 CST 2005


The real determining factor is the size of the field where it's being stored.  I would run something like:

SELECT Max(Len(CAST(KeyStatsComment AS VarChar(8000)))) FROM ......

That will give you the Max length of the existing data, then from there you should be able to determine how many characters you need in the field in the table.

I think you may be confusing VarChar and Char, VarChar is variable length and stores only what it has, Char is fixed length and stores the full amount of characters.

-Chris Mackin

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steven
W. Erbach
Sent: Thursday, January 06, 2005 11:53 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Convert from ntext to varchar


Chris,

I was able to modify the SQL statement using your CAST suggestion. However when I executed the sproc, the NewKeyStatsComment field only had 16 characters of data in it.

My understanding of the varchar data type is that it's variable in length, like the ntext type. My table definition has ntext(16) for the current KeyStatsComment column. Of course, the comments are longer than 16 characters and they show up just fine in the table. But I've defined the NewKeyStatsComment column as varchar(16).

I suppose if I modified the NewKeyStatsComment to varchar(8000) then the entire comment would fit in the field. But doesn't SQL Server then bulk up the table by the 8000 characters per row? Or what?

Steve Erbach
Neenah, WI

> ------------Original Message------------
> From: "Mackin, Christopher" <CMackin at quiznos.com>
> To: dba-sqlserver at databaseadvisors.com
> Date: Thu, Jan-6-2005 9:00 AM
> Subject: RE: [dba-SQLServer] Convert from ntext to varchar
> 
> Try:
> CAST(KeyStatsComment AS varchar(16))
> 
> Not specifying a length for the VarChar field may be causing the 
> problem.
> 
> -Chris Mackin


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list