Earlier I have written about the SQL Server 2008 and its tools and pointed out the best improvements in this new release. After a while I have on several occasions bumped into one very annoying thing, when designing databases using the Management Studio.
Suppose you have created a table using the designer. The table has a column ID, set as primary key, but you forgot to specify this column to be “Identity Specification = Yes”. Open the table in design view and set the ”Identity Specification = Yes” and try to save the changes… In my case it fails every time even though the table is empty and contain no data at all, giving the error message:
“Saving changes is not permitted. The changes you have made…. …. or enabled the option Prevent saving changes that require…“, [Cancel] [Save Text File].
Finally I found the option to be modified!!!
The error message
The error message looks like this:
The error is easy to re-produce:
- Create a table, one column specified as primary key of integer type.
- Save the table.
- Open the table, set the primary key to be “Identity Specification = Yes”.
- Save the modified table.
The solution
The option was not easy to find, but here it is:
On the Tools menu in Management Studio:
Options… -> Designers and uncheck “Prevent saving changes that require table re-creation“.
This is the check box to look for:
Uncheck this and try to save the modified table once more. In my case it was a great success!
More posts about the SQL Server 2008
I have written about the SQL Server 2008 in the following posts:
-
-
-
A friend of mine just emailed me one of your articles from a while back. I read that one a few more. Really enjoy your blog. Thanks
Thanks for the tip!
This have been an annoyance since I first installed SQL Server 2008, so getting a solution for this is quite a treat.
thanks man for solving this annoying issue
Thanks you!!!!! You just saved me a lot of teeth grinding.
Thanks again! I think this is the third time this article have saved me a lot of grief.
HI MATE, THANKS U SOLVED ME SOME HASSLE WITH THIS ONE
THANKS VERY MUCH
Thanks for that great tip, you saved my life. It was getting annoying to drop and recreate the table.
Thanks again.
Thanks for the tip!
Haha, äntligen. Tack för tipset!
That was wonderful, Thanks for the help, I was struggling to get this sorted out
Thank you very much for your tip.
I lost two days trying to figure out how to enable the identity specification.