Alter Table Alter Column sometimes fails when changing to Not

Hi Martin,
I think that solves my problem -- perhaps slightly indirectly. In the Alter
statements I did not specify the datatype because I was not changing it.
Perhaps the statements defaulted to something other than the float(8) of the
original columns?
I'll try explicitly repeating the current datatype and see if that makes the
message disappear.
Follow-up question: If I drop the offending "DF__Temporary__..." constraints
do they get recreated automatically?
Thanks much and Best regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> I think the error message raises when you change a column definition from
> float(8) to int, that is having a default value of data type "float(8)".
> Use "sp_help tblVendQuotePrice" to check the constraints on the table
> tblVendQuotePrice.
> Check the constraint_type and constraint_keys columns from the last result
> as returned from sp_help, and check the default values.
> Upon altering the column definition , you will need to drop the default
> constraint "DF__Temporary__VQuot__22B77893" when the data type of the colu
mn
> does not match the data type of the default value.
> ALTER TABLE tblVendQuotePrice DROP CONSTRAINT DF__Temporary__VQuot__22B778
93
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@.microsoft.com ¤¤???g...
> tables:
> the
>
>> In the Alter
> statements I did not specify the datatype because I was not changing it.

I see datatype 'int' as specified in the ALTER TABLE statement from original
post. Perhaps the wrong datatype (int instead of float) was inadvertently
specified.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
There is no automatic recreation of constraints using Transact-SQL scripts.
If you want to change the column datatype, you need to drop constraints
referencing the column, alter the column and then recreate the constraints.
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
> Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
> the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
> the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>|||Please refer to BOL for more information.
- CREATE DEFAULT
- sp_binddefault
- sp_unbinddefault
- DROP DEFAULT
When you create a column with a default value (either by using CREATE
TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
"default" automatically. This default object will then bound to a column.
To verify this, you can obtain the list of default objects from the
following query. You can find your "DF__Temporary__..." default objects from
the result.
SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
name
You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
After creating the default objects, you can use sp_binddefault and
sp_unbinddefault to bind/unbind the default objects to your columns.
For the current case, the "DF__Temporary__..." default objects will *not* be
binded to your columns automatically.
You will need to create a default object (using CREATE DEFAULT) and bind it
to the column (using sp_binddefault).
Martin C K Poon
Senior Analyst Programmer
====================================
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> bl
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com g...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>
from
result
column
DF__Temporary__VQuot__22B77893[color=dar
kred]
fails
make
objects
explicitly
error.|||Thanks much, Martin.
Best Regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> Please refer to BOL for more information.
> - CREATE DEFAULT
> - sp_binddefault
> - sp_unbinddefault
> - DROP DEFAULT
> When you create a column with a default value (either by using CREATE
> TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
> "default" automatically. This default object will then bound to a column.
> To verify this, you can obtain the list of default objects from the
> following query. You can find your "DF__Temporary__..." default objects fr
om
> the result.
> SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
> name
> You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
> After creating the default objects, you can use sp_binddefault and
> sp_unbinddefault to bind/unbind the default objects to your columns.
> For the current case, the "DF__Temporary__..." default objects will *not*
be
> binded to your columns automatically.
> You will need to create a default object (using CREATE DEFAULT) and bind i
t
> to the column (using sp_binddefault).
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com ¤¤???g...
> Alter
> the
> the
> constraints
> from
> result
> column
> DF__Temporary__VQuot__22B77893
> fails
> make
> objects
> explicitly
> error.
>
>