Alter statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alter statement

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 03:03:25

Hi all,

when i try to alter a column
i get the error

ALTER TABLE ALTER COLUMN ChangeDate failed because one or more objects access this column.

the Query is
ALTER TABLE MasterAuditTable
ALTER COLUMN ChangeDate SMALLDATETIME NOT NULL

Pls help?

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 03:10:25

I was able to alter the table manually by going to object explorer, clicking on table and changing the data type

but iam unable to alter it through query

sounds amazing for me. can any one spot out. thanks in advance

Iam a slow walker but i never walk back

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-30 : 04:36:05

i think there is data with null value in ur changedate column so that u got the error

ur changing the changedate to smalldatetime type what is the initial datatype of changedate column?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-30 : 04:51:47

U Can Change the NULL to Not NULL, By Using Default constriant

create table tbl( id int)

insert into tbl select 2

select * from tbl

alter table tbl add id2 int NOT null default(0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-30 : 05:21:21

quote:
Originally posted by dineshrajan_it

Hi all,

when i try to alter a column
i get the error

ALTER TABLE ALTER COLUMN ChangeDate failed because one or more objects access this column.

the Query is
ALTER TABLE MasterAuditTable
ALTER COLUMN ChangeDate SMALLDATETIME NOT NULL

Pls help?

Iam a slow walker but i never walk back

If the table has already data, you cant add new column without specifying default value

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 07:33:09

Thanks for response,

I changed the alter statement for existing column as

ALTER TABLE MasterAuditTable
ALTER COLUMN ChangeDate SMALLDATETIME NOT NULL DEFAULT GETDATE()

but still error persist

Iam a slow walker but i never walk back

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 07:40:30

I found out the problem

since i already defined a Default constraint for datetime column

when i tried to change it to smalldatetime it threw a error bcoz of already existing constraint. i removed the default constraint and alter column . now works fine.

Thanks

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources