Problem Altering an existing column

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 2000 Forums

Transact-SQL (2000)

Problem Altering an existing column

Author

Topic

Ravi0435
Starting Member

47 Posts

Posted - 2008-09-08 : 16:49:46

Hi,
I want to change a data type of a column from char(1) to char(3) and here is what i am trying to execute:

ALTER TABLE MEMBER_DETAIL
ALTER COLUMN [MEMBER_TYPE] char(3) null

And here is the Error:

Server: Msg 5074, Level 16, State 8, Line 1
The statistics 'MEMBER_TYPE' is dependent on column 'MEMBER_TYPE'.

Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN MEMBER_TYPE failed because one or more objects access this column.

The same command works for STAGING Table

ALTER TABLE MEMBER_DETAIL_STG
ALTER COLUMN [MEMBER_TYPE] char(3) null

Any suggestion/advice would be appreciated.

thanks,
ravi

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-08 : 17:42:14

Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN MEMBER_TYPE failed because one or more objects access this column

The error is occurs since the field [MEMBER_TYPE] referenced in other object.

hey

Ravi0435
Starting Member

47 Posts

Posted - 2008-09-09 : 09:38:35

Hi,
Thanks for the reply.

What does it mean by referenced in other object?
there are a bunch of Stored Procedures/Function in my application which may be referencing this table and this column in particular,does this mean it is being referenced..??

What may be the other objects which are referencing..??

And other thing i just noticed is that the column has data in it i.e there are 50% of records in this table have data 'F' or 'S'.
Do you think this might be causing me the prblem of not allowing me to change the datatype from char(1) to char(3)?

Thanks,
ravi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 09:51:17

Is the column MEMBER_TYPE referenced by other tables?

EXEC sp_help MEMBER_DETAIL

Madhivanan

Failing to plan is Planning to fail

Ravi0435
Starting Member

47 Posts

Posted - 2008-09-09 : 10:08:52

Hello Madhivanan:
thanks for the reply,

I executed that command but what shud I be checking
Here is what i got at the bottom of the output for that command:

No foreign keys reference this table.
No views with schema binding reference this table.

please let me know if there is any particular info i need to check as there is a bunch of stuff it returns.

thanks
R

Ravi0435
Starting Member

47 Posts

Posted - 2008-09-09 : 13:25:19

Here is the Answer:

According to the error message, I had statistics created on the column, i needed to drop the existing statistics first:

http://msdn.microsoft.com/en-us/library/ms175075.aspx

Secondly, i ran sp_depends Member_Detail to get a list of all items which depend on the table, from there i could see what additional items may/maynot be causing the error.

http://msdn.microsoft.com/en-us/library/aa933275(SQL.80).aspx

since i didnt have any other dependents i made the reqd modification and recreated the statistics.

thanks for guys who replied.

Thanks,
R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources