"Doug MacLean" <DougMacL***@discussions.microsoft.com> ¦b¶l¥ó
news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@microsoft.com ¤¤¼¶¼g...
> I have created and run scripts to alter numerous numeric fields in 19
tables:
> 1. To set any null values on those fields to zero
> 2. To then change the properties of those fields to Not Null
>
> All of the fields are either int(4) or float(8).
>
> Frequently, but not always, the Alter statement on the float fields fails
> and I have to go into the table in Enterprise Manager, whereby I can make
the
> change OK.
>
> Here is a typical example of the statements in Query Analyzer:
>
> UPDATE tblVendQuotePrice
> SET VQuoteTurn = 0
> WHERE VQuoteTurn is null
> Go
> ALTER TABLE tblVendQuotePrice ALTER COLUMN VQuoteTurn int NOT Null
> Go
>
> And here is the message that results.
>
> Msg 5074, Level 16, State 1, Line 2
> The object 'DF__Temporary__VQuot__22B77893' is dependent on column
> 'VQuotePrice'.
> Msg 4922, Level 16, State 9, Line 2
> ALTER TABLE ALTER COLUMN VQuotePrice failed because one or more objects
> access this column.
>
> The 'DF__Temporary ... object is a Constraint that I did not explicitly
> create. Other fields with similar constraints do NOT give me the error.
>
> Can someone suggest a way to eliminate the error condition?
> --
> 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 column
> does not match the data type of the default value.
> ALTER TABLE tblVendQuotePrice DROP CONSTRAINT DF__Temporary__VQuot__22B77893
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacL***@discussions.microsoft.com> ¦b¶l¥ó
> news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@microsoft.com ¤¤¼¶¼g...
> > I have created and run scripts to alter numerous numeric fields in 19
> tables:
> > 1. To set any null values on those fields to zero
> > 2. To then change the properties of those fields to Not Null
> >
> > All of the fields are either int(4) or float(8).
> >
> > Frequently, but not always, the Alter statement on the float fields fails
> > and I have to go into the table in Enterprise Manager, whereby I can make
> the
> > change OK.
> >
> > Here is a typical example of the statements in Query Analyzer:
> >
> > UPDATE tblVendQuotePrice
> > SET VQuoteTurn = 0
> > WHERE VQuoteTurn is null
> > Go
> > ALTER TABLE tblVendQuotePrice ALTER COLUMN VQuoteTurn int NOT Null
> > Go
> >
> > And here is the message that results.
> >
> > Msg 5074, Level 16, State 1, Line 2
> > The object 'DF__Temporary__VQuot__22B77893' is dependent on column
> > 'VQuotePrice'.
> > Msg 4922, Level 16, State 9, Line 2
> > ALTER TABLE ALTER COLUMN VQuotePrice failed because one or more objects
> > access this column.
> >
> > The 'DF__Temporary ... object is a Constraint that I did not explicitly
> > create. Other fields with similar constraints do NOT give me the error.
> >
> > Can someone suggest a way to eliminate the error condition?
> > --
> > Doug MacLean
>
>
>

"Doug MacLean" <DougMacL***@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:
>
>> 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
>> column
>> does not match the data type of the default value.
>> ALTER TABLE tblVendQuotePrice DROP CONSTRAINT
>> DF__Temporary__VQuot__22B77893
>> --
>> Martin C K Poon
>> Senior Analyst Programmer
>> ====================================
>> "Doug MacLean" <DougMacL***@discussions.microsoft.com> ¦b¶l¥ó
>> news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@microsoft.com ¤¤¼¶¼g...
>> > I have created and run scripts to alter numerous numeric fields in 19
>> tables:
>> > 1. To set any null values on those fields to zero
>> > 2. To then change the properties of those fields to Not Null
>> >
>> > All of the fields are either int(4) or float(8).
>> >
>> > Frequently, but not always, the Alter statement on the float fields
>> > fails
>> > and I have to go into the table in Enterprise Manager, whereby I can
>> > make
>> the
>> > change OK.
>> >
>> > Here is a typical example of the statements in Query Analyzer:
>> >
>> > UPDATE tblVendQuotePrice
>> > SET VQuoteTurn = 0
>> > WHERE VQuoteTurn is null
>> > Go
>> > ALTER TABLE tblVendQuotePrice ALTER COLUMN VQuoteTurn int NOT Null
>> > Go
>> >
>> > And here is the message that results.
>> >
>> > Msg 5074, Level 16, State 1, Line 2
>> > The object 'DF__Temporary__VQuot__22B77893' is dependent on column
>> > 'VQuotePrice'.
>> > Msg 4922, Level 16, State 9, Line 2
>> > ALTER TABLE ALTER COLUMN VQuotePrice failed because one or more objects
>> > access this column.
>> >
>> > The 'DF__Temporary ... object is a Constraint that I did not explicitly
>> > create. Other fields with similar constraints do NOT give me the error.
>> >
>> > Can someone suggest a way to eliminate the error condition?
>> > --
>> > Doug MacLean
>>
>>
>>

"Doug MacLean" <DougMacL***@discussions.microsoft.com> ¦b¶l¥ó
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:
>
> > 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
column
> > does not match the data type of the default value.
> > ALTER TABLE tblVendQuotePrice DROP CONSTRAINT
DF__Temporary__VQuot__22B77893
> > --
> > Martin C K Poon
> > Senior Analyst Programmer
> > ====================================
> > "Doug MacLean" <DougMacL***@discussions.microsoft.com> |b?l¢Do
> > news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@microsoft.com ?????g...
> > > I have created and run scripts to alter numerous numeric fields in 19
> > tables:
> > > 1. To set any null values on those fields to zero
> > > 2. To then change the properties of those fields to Not Null
> > >
> > > All of the fields are either int(4) or float(8).
> > >
> > > Frequently, but not always, the Alter statement on the float fields
fails
> > > and I have to go into the table in Enterprise Manager, whereby I can
make
> > the
> > > change OK.
> > >
> > > Here is a typical example of the statements in Query Analyzer:
> > >
> > > UPDATE tblVendQuotePrice
> > > SET VQuoteTurn = 0
> > > WHERE VQuoteTurn is null
> > > Go
> > > ALTER TABLE tblVendQuotePrice ALTER COLUMN VQuoteTurn int NOT Null
> > > Go
> > >
> > > And here is the message that results.
> > >
> > > Msg 5074, Level 16, State 1, Line 2
> > > The object 'DF__Temporary__VQuot__22B77893' is dependent on column
> > > 'VQuotePrice'.
> > > Msg 4922, Level 16, State 9, Line 2
> > > ALTER TABLE ALTER COLUMN VQuotePrice failed because one or more
objects
> > > access this column.
> > >
> > > The 'DF__Temporary ... object is a Constraint that I did not
explicitly
> > > create. Other fields with similar constraints do NOT give me the
error.
> > >
> > > Can someone suggest a way to eliminate the error condition?
> > > --
> > > 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" <DougMacL***@discussions.microsoft.com> ¦b¶l¥ó
> 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:
> >
> > > 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
> column
> > > does not match the data type of the default value.
> > > ALTER TABLE tblVendQuotePrice DROP CONSTRAINT
> DF__Temporary__VQuot__22B77893
> > > --
> > > Martin C K Poon
> > > Senior Analyst Programmer
> > > ====================================
> > > "Doug MacLean" <DougMacL***@discussions.microsoft.com> |b?l¢Do
> > > news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@microsoft.com ?????g...
> > > > I have created and run scripts to alter numerous numeric fields in 19
> > > tables:
> > > > 1. To set any null values on those fields to zero
> > > > 2. To then change the properties of those fields to Not Null
> > > >
> > > > All of the fields are either int(4) or float(8).
> > > >
> > > > Frequently, but not always, the Alter statement on the float fields
> fails
> > > > and I have to go into the table in Enterprise Manager, whereby I can
> make
> > > the
> > > > change OK.
> > > >
> > > > Here is a typical example of the statements in Query Analyzer:
> > > >
> > > > UPDATE tblVendQuotePrice
> > > > SET VQuoteTurn = 0
> > > > WHERE VQuoteTurn is null
> > > > Go
> > > > ALTER TABLE tblVendQuotePrice ALTER COLUMN VQuoteTurn int NOT Null
> > > > Go
> > > >
> > > > And here is the message that results.
> > > >
> > > > Msg 5074, Level 16, State 1, Line 2
> > > > The object 'DF__Temporary__VQuot__22B77893' is dependent on column
> > > > 'VQuotePrice'.
> > > > Msg 4922, Level 16, State 9, Line 2
> > > > ALTER TABLE ALTER COLUMN VQuotePrice failed because one or more
> objects
> > > > access this column.
> > > >
> > > > The 'DF__Temporary ... object is a Constraint that I did not
> explicitly
> > > > create. Other fields with similar constraints do NOT give me the
> error.
> > > >
> > > > Can someone suggest a way to eliminate the error condition?
> > > > --
> > > > Doug MacLean
> > >
> > >
> > >
>
>
>