0

I'm using Microsoft SQL Server Management Studio, after I have modified column type from varchar to int I tried to update the table but it throw the following error

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created. (list of 3 tables have a relation with this table as fk)

I tried to fix it by Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation” from this question

Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

then the update not throw any error but It throw the following error when open table design

Catastrophic failure (Exception from(E_UNEXPECTED)) (SQLEditors)) after modifying column type

I tried this but also the same error

RegSvr32 msxml3.dll

RegSvr32 msxml6.dll

Community
  • 1
  • 1
programmer
  • 109
  • 1
  • 7
  • 3
    before you changed column type did you confirm that all the data in the column is a valid data type for the new column? Meaning you dont have letters in a filed you are trying to convert to INT? Another way to do this is to create a new column in the table with the correct datatype, then update the new column with the values from the old column, then drop the old column, and finally rename the new column to what the old columns name was – Brad Apr 13 '18 at 12:20
  • @Brad yes all values is integer (1,2,3..) , Another way to do this is to create a new ... (how to do that the table design not opened) – programmer Apr 13 '18 at 14:03
  • Not clear on what your saying/asking? The table design not opened? – Brad Apr 13 '18 at 14:05
  • @Brad when I tried to open table design it throw the second error in my question – programmer Apr 13 '18 at 14:08
  • Ok, is there anyway you can revert your changes back to what they were before you made any changes? This may fix the current error then you can look at doing the process/data change a diffderent way – Brad Apr 13 '18 at 14:09
  • but select top 1000 rows it open the table data correctly – programmer Apr 13 '18 at 14:10
  • Without using the UI interface you can alter a table by typing ALTER TableName ADD COLUMNNAME DataType – Brad Apr 13 '18 at 14:11
  • I would revert your changes back and make sure everything is worknig/stable as it was before you made your changes. Then I can help develope a solution – Brad Apr 13 '18 at 14:12
  • thanks @Brad after restart the studio the changes has removed then I have applied your device for alter table . can you please write that as answer so I can accept it? – programmer Apr 13 '18 at 14:29

1 Answers1

0

per comments updating here:

Revert back the changes to what they were before the error and work on a differnt solution to fix the problem.

One alternative is:

  1. Create new column in table that you want the data to be inserted into/converted to
  2. Update that new column from the existing column you have the data in you are trying to convert from.
  3. Verify the data in the new column is correct/works good.
  4. Drop the old column.
  5. Rename the new column to the old columns name.
Brad
  • 3,454
  • 3
  • 27
  • 50