I am sure this isn't a bug as such, but it is a bit of a pain.

If you have:

Table1
Field1 VARCHAR(20)

And you write:

ALTER TABLE1
ALTER Field1 as VARCHAR(10)

any field with more than 10 characters is mapped into the new version of the field as NULL i.e. field such as: "Something" maps over as "Something" but a field such as "Something Else" doesn't map over as "Something E" it maps over as NULL.

At least this is the experience I have just had with a field during a somewhat complicated multi-stage ALTER process.

Am I confused or is that what happens?
If it is how should I avoid it?

Adam


Not actually noticed that myself, probably haven't done it but I think I can explain it.

Its not a bug (unfortunately) but adherence to the standard.

Tim has pretty much adhered to the standard (2003 I think) and one of the horrible things it says is that if the string you're trying to put in it a warning or error should be raised. Tim doesn't have any way to raise a warning which you can ignore so has to raise an error.

Having said that my guess as to what's happening is that Tim is ignoring the error and just using a NULL.

The way round it is to preprocess the column to reduce the length to the new maximum.

Roy Lambert [Team Elevate]

Adam,

<< I am sure this isn't a bug as such, but it is a bit of a pain.

If you have:

Table1
Field1 VARCHAR(20)

And you write:

ALTER TABLE1
ALTER Field1 as VARCHAR(10)

any field with more than 10 characters is mapped into the new version of the
field as NULL i.e. field such as: "Something" maps over as "Something" but a
field such as "Something Else" doesn't map over as "Something E" it maps
over as NULL. >>

You should see some log messages like this:

The value for the Test column has been set to NULL for the row 1 due to an
error (An error occurred with the value This is a  (The value would result
in truncation))

provided that you have an OnLogMessage event handler set for the TEDBQuery
component that you're using.

--
Tim Young
Elevate Software
www.elevatesoft.com