From: Ebbe on
I tried the following statement:
“ALTER TABLE STOCKTRANS ALTER COLUMN DATASET varchar(3) NOT NULL”

I got this message in return:
“-->
Msg 5074, Level 16, State 1, Line 1
The index 'I_013ITEMNUMBERIDX' is dependent on column 'DATASET'.
..
..
..
Msg 5074, Level 16, State 1, Line 1
The index 'I_013SEQNO' is dependent on column 'DATASET'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN DATASET failed because one or more objects access
this column.
<--“
As far as I understand the message above, it is not possible to use this
command, when the column is a part of one or more indexes.

How can I check and uncheck “Allow Nulls” in SQL Server Management Studio
(SSMS) without having the same problems?

I have been told that all what I can do in SSMS, can be done by
commands/statements too.

From: Erland Sommarskog on
Ebbe (Ebbe(a)discussions.microsoft.com) writes:
> I tried the following statement:
> �ALTER TABLE STOCKTRANS ALTER COLUMN DATASET varchar(3) NOT NULL�
>
> I got this message in return:
> �-->
> Msg 5074, Level 16, State 1, Line 1
> The index 'I_013ITEMNUMBERIDX' is dependent on column 'DATASET'.
> .
> .
> .
> Msg 5074, Level 16, State 1, Line 1
> The index 'I_013SEQNO' is dependent on column 'DATASET'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN DATASET failed because one or more objects access
> this column.
><--�
> As far as I understand the message above, it is not possible to use this
> command, when the column is a part of one or more indexes.

Correct, you need to drop the indexes first in this case. Hade you
only made the column longer, but not changed the nullability, you
would have gotten away with it.

> How can I check and uncheck �Allow Nulls� in SQL Server Management Studio
> (SSMS) without having the same problems?

Because behind your back, SSMS creates a new copy of the table, and
copies data over, and then moves all dependent objects.

Which is a far more complex and dangerous operation if not done right -
and SSMS does not do it right. There are several very serious flaws
with this funcationality in SSMS. As on example, say that you would
use the Table Designer to change the data type from varchar(3) to
varchar(5), which can be done with ALTER TABLE; SSMS would still drop
the table and all that for you.

In your case, I would recommend that you bring out your index definitions
from your source-code repository, or script them if you don't have
them. Then drop the indexes, alter the column, and recreate the indexes.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Ebbe on
Hi Erland

Thank You for the quick answer.
I had hoped for an easier way to solve my problem.

Are there other objects than indexes, that I have to have in mind, when I
wish to change the NULL option on a column?
From: Erland Sommarskog on
Ebbe (Ebbe(a)discussions.microsoft.com) writes:
> Thank You for the quick answer.
> I had hoped for an easier way to solve my problem.
>
> Are there other objects than indexes, that I have to have in mind, when I
> wish to change the NULL option on a column?

In the topic of ALTER TABLE in Books Online, you find a whole slew of
exceptions in the section for ALTER COLUMN. I cannot vouch for that it is
100% correct, but if there are deviations, you will find as you try.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Ebbe on
Hi Erland

Tanks a lot.
You have been a great help :-)