alter column length

orderno is varchar(64), i need to make it varchar(100)

mID column is computed - based on a formula which includes orderno
i also have a pk which is based on orderno and one other field

i thought i could do this for the pk:

alter table TABLENAME nocheck constraint PKNAME
go
alter table TABLENAME alter column orderno varchar(100)
go
alter table TABLENAME check constraint PKNAME
go

it fails w/the same error i get without tryng to disable the constraint:

Server: Msg 5074, Level 16, State 2, Line 1
The column 'Mid' is dependent on column 'orderno'.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'PKNAME' is dependent on column 'orderno'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN orderno failed because one or more objects access this column.

What is the correct way to disable that constraint so i can increase the column length?
also, how do i get around that computed column?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to drop the PK constraint, change the column, and recreate the PK. no way around that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
yeah, that's what i figured.  i just wanted to make sure there wasn't some magic piece i wasn't aware of.  what of the computed column, angelll?  do you know how i get around that?
0
dbaSQLAuthor Commented:
does the computation have to be dropped?  and even so, how would i do that?  it's created at table creation, like this:

create table TABLENAME (
 mID AS (orderno + '.' + convert(varchar(25),timestampdate,121)),
 .....
......


can you advise, angelll?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, the computed column has to be dropped also and added afterwards
0
dbaSQLAuthor Commented:
eeewgh... yuck
i need to drop the computed column, then drop the constraint,
alter column length
add the computed column back (with formulat in place)
recreate the constraint

problem number 1, i don't want to get into my syscolumns to get mID back up to the 1st column in the table.  well, i do want to, actually  ....i'm just not certain i have the best means of doing so

---allow updates
---add the column, with the formula
---update syscolumns, changing colid and colorder for new column
---disallow updates

is that it?  do you think i've missed anything?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if there is any trigger on the table... or any view on the table, you need to recompile those...
0
dbaSQLAuthor Commented:
no triggers, there is one view.  ugh
0
dbaSQLAuthor Commented:
--allow updates
--drop column, drop pk constraint
--adjust length of orderno column
--recreate mID column, with the formula
--update syscolumns, changing colid and colorder for new column
--recreate pk
--disallow updates
--exec sp_recompile viewname

you think that's it, angell?  i gotta do this on 11 different instances tonight after production orderflow ceases.  i need to be sure i'm not forgetting anything
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to be 100% sure, backup/restore one of the databases, and try to run that as script there...
that way, you will be sure.
0
dbaSQLAuthor Commented:
yep.  agreed completely
i'll let you know how it goes after the fact

thank you, angelll
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.