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
 Old Forums
 CLOSED - General SQL Server
 Alter column length problem !

Author  Topic 

nhamdan
Starting Member

19 Posts

Posted - 2005-06-06 : 05:31:04
I have this statment that i tried to run on sql server, but it failed

ALTER TABLE tbl_inv_det ALTER COLUMN inv_no numeric(9)

the column originaly type numeric(5) and i just want to increase the length of it to 9.

it gives me the following error:

Server: Msg 5074, Level 16, State 8, Line 1
The object 'PK_TBL_INV_DET' is dependent on column 'inv_no'.


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


AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 05:51:01
You cant use this statement directly as it is part of a primary key
Look up ALTER TABLE in BOL

I dont know the level of your SQL expertise but to see how SQL server does this change the column in design view via EM then click the "Save change script" icon. This will show you what it is about to execute.

You will probably find that it creates a new table and then applies the Primary Key on the new table.
It can be done differently by dropping the constraints 1st then reapplying them after the column has been altered.

Hope that makes sense

Andy

Beauty is in the eyes of the beerholder
Go to Top of Page

nhamdan
Starting Member

19 Posts

Posted - 2005-06-06 : 05:56:04
Thanx alot your solution seems excellant but how to do this :

" design view via EM then click the "Save change script" icon "

Please what is EM & how to do this in more details
Go to Top of Page

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 06:02:32
EM is Enterprise Manager its the GUI for SQL Server

Go into the relevant database > Tables then right click the table and click design view

Andy

Beauty is in the eyes of the beerholder
Go to Top of Page

nhamdan
Starting Member

19 Posts

Posted - 2005-06-06 : 09:57:48
Okay, i went to EM and right click the tabel for design view but i havent seen any option that i can save from the changes script that i have made ?

Go to Top of Page

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 10:20:44
Its next to the "cut" icon, the tool tip is "Save change script"



Beauty is in the eyes of the beerholder
Go to Top of Page

nhamdan
Starting Member

19 Posts

Posted - 2005-06-06 : 11:05:02
Thaaaaaaaaaaaaaaaanx alot
Go to Top of Page
   

- Advertisement -