August 30, 2008 at 5:56 am
There are a couple parts to this post.
1. I imported two databases from SQL 2005 express into 2008 Express. Each table in each database had an Identity field. During the import the Identity property was turned off. How do I do the import again without losing the identity property? Could I just detach and attach the SQL 2005 database to prevent this behavior? Or is there a third way to do this?
2. When I go to increase the field size in Design mode, I receive the message "Save Changes not permitted... without dropping and re-creating the table. ...enable the option Prevent saving changes...". In SQL 2005 and 2000 I can change field lengths without dropping the table and losing the data. How do I do this in 2008 express? And where is the option to Prevent Saving Changes?
Thanks,
pat
August 30, 2008 at 8:38 am
1. I should have backed up and restored. This solved the missing identity problem.
2. I disabled the Tools>options>Designers>table and database designers>Prevent saving changes that require table recreation.
This name is a bit misleading. After disabling it, I was able to increase a varchar field length and even restore the identity fields without loss of data.
August 30, 2008 at 8:56 am
mpdillon (8/30/2008)
This name is a bit misleading. After disabling it, I was able to increase a varchar field length and even restore the identity fields without loss of data.
Actually, the name is completely accurate: those kind of changes to tables ARE implemented in SSMS by dropping and recreating the table (just not in that order) in such a way that the data is preserved. If you want to see proof of that or how it is done, just click the "Script" button before you save your changes to the table.
As for why this option is there: It is not because you could lose your data, rather it is because on large tables the Copy step (that preserves your data) can be extremely slow and lock up your DB or Server for a long time (I have seen it take many hours).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply