Altering a on attribute data size from a table in SQL Server

views:

answers:

Altering a on attribute data size from a table in SQL Server

So I'm trying to do something I thought would've been straightforward. I have a table in the DB named "Images." It's 'Description' property is of type nvarchar(50). I simply want to make it nvarchar(250). Every time I try, it says it can't save because some tables would have to be redropped. I can't just delete it (i think) because, there's already data being maintained by it, and I can't lose it.

EDIT::

Exact error message

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

Should I just disable the 'Prevent saving changes that require table re-creation' and save it from there.

Do you have any tables referencing the "Description" column? That would prevent you from changing the data type/length.

Dave7896 2010-01-25 21:15:48

no, the 'Description' column is not a FK anywhere.

Justen 2010-01-26 05:53:53

+2 A:

This KB article explain it

Ray 2010-01-25 21:19:28

I don't entirely understand. I'm not sure what the tracking thing is, but let's say I did just turn off that safety, when I change the data type of one of the columns, do I lose all data associated with that? Basically it deletes the table and all it contained data, then recreates it?

Justen 2010-01-26 06:00:28

You won't lose any data. The best way to see what happens is to make some changes in the table designer, the right-click and select 'Generate Change Script'. If you made a minor change to the table, you will see a simple 'alter table' or something. For a more complex change, you will see a script that creates a temp table, loads all the data from your existing table into it, drops your existing table, creates a new replacement table with the new structure, and then inserts the data from the temp table into the new table.

Ray 2010-01-26 15:24:18

Alright cool. I'll try it work on Monday and be back to comment/mark as answer then.

Justen 2010-01-26 18:25:42

Tried it out, and I get the same error message. It doesn't generate any script and just gives me the error I updated in my OP

Justen 2010-01-27 13:21:51

oh right, sorry - for this experiment to work, you need to turn off that setting described in the KB article ("Prevent saving changes that require the table re-creation" on the designer tab of the options window). Then you can see what type of code ssms generates, and whether or not you want to use it or roll your own.

Ray 2010-01-27 13:45:55

They seem to describe that practice with much reserve saying that I could lose some data. I did it with my non-production DB and it went fine, but for future reference, what are the chances that something like that would happen?

Justen 2010-01-27 13:51:15

I have never lost any data running those scripts, except one time when I thought I would 'improve' it by tweaking a couple of things and running it one statement at a time. So I think the chances are slim that you would lose anything, but I would have a backup handy...

Ray 2010-01-27 14:00:01

Were you doing this from the SSMS GUI or were you running a script using alter table to make the change? IF you did it through the designer, I believe it creates another table, drops the orginal and renames the new table. If that table is in a PK/FK relationship. it can't drop the table. Never make table changes except by using a script. YOu also need these to properly put them in source control as well.

HLGEM 2010-01-25 21:21:38

I was attempting to do this in a the design view. Where you right-click on a table in MS SQL Server and choose design and it shows you all the columns for that table and their datatype. The table does have a column that is a PK and one that is a FK. Not sure how to make a script to change the size

Justen 2010-01-26 05:55:29

ansaurus

tags:

views:

answers:

Altering a on attribute data size from a table in SQL Server

related questions