can't alter column to NOT NULL

If I run the following 3 statements (on sql server 2000):
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:

> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql

Cannot Update Records that contain NULL Values!

Hi,

I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one.

Any Ideas on how to get around this problem?

THanks

Then what's wrong? Since you delete the data from the field, it should be set to NULL. What do you expect to get after deleting the data? And what's meaing of "im back to square one"|||Fix your update statement. If you've used the wizard with "Check Original Values", then you are correct, it fails with nulls. You have to manually modify the update to handle nulls.|||

yep.. i got it now... i had to change "compare all values" to "overwrite changes". That makes sense. you cant compare null values to each other.

Thanks

Cannot set numeric to null before inserting to table

Hi,

I have a flat file whose data looks like this:

"Opening Balance", Acct1234, 1001.01

"Closing Balance", Acct1234, 1001.01

In my script component for "Opening Balance", I set my output columns like this:

Row.AccountNumber = CDate(rowValues(1)

Row.OpeningBalance = CDec(rowValues(2))

Row.ClosingBalance = Nothing

Because I don't have a value for ClosingBalance in this row, I set ClosingBalance = Nothing.

And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.

Row.AccountNumber = CDate(rowValues(1)

Row.OpeningBalance = Nothing

Row.ClosingBalance = Nothing

Then, in my column mappings I map OpeningBalance = OpeningBalance and ClosingBalance = ClosingBalance.

The idea being that if there's no value, then it should be set to NULL. However, in my table, I see that instead of NULLs, there's 0.00000. Which is not what I want to see.

Any ideas why this is happening?

Thanks

Oops, I meant:

And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.

Row.AccountNumber = CDate(rowValues(1)

Row.ClosingBalance = CDec(rowValues(2)

Row.OpeningBalance = Nothing

|||

After sniffing around a little bit, it seems that one can't set anything but a String to Nothing, ie) NULL?

Is this true? Seems to be the case here anyways. When I set my empty values to -999 they show up beautifully in the table, so it's definitely ignoring my request to set to NULL.

Is there a way to force this to work?

|||Try Row.ClosingBalance_IsNull = True.|||Duh, forgot about that!