September 9, 2008 at 7:53 am
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
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 problem of not allowing me to change the datatype from char(1) to char(3)?
Any suggestion/advice would be appreciated.
thanks
R
September 9, 2008 at 8:56 am
Sounds as though someone has manually created some statistics on the column using CREATE STATISTICS.
Just drop the statistics using
DROP STATISTICS MEMBER_DETAIL.MEMBER_TYPE
before altering the column
September 9, 2008 at 11:27 am
thanks Mr. or Ms.500
you were right....
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,
R
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply