how can i change a field in my SQL databse from numeric(18) to varchar(10)

Question

i have a zipcode field in a databsae that i just took over. previously it was set as a numeric field with 18 precision but i am trying to convert it over to varchar(10).

i need to make this change because the linq field are coming in as decimal and are causing issues and i want to change the linq field to simply be strings.

i tried this in SQL server enterprise manager but i get this error, saying:

that the table will have to be dropped and recreated. you have either made changes to a table that can't be recreated or enable the option to prevent saving changes that require a table recreation

any suggestions.

Answer 1

to enable that option in SQL management studio uncheck the following option...

Tools / Options / Designers / Table and Database Designers / Prevent saving changes that require table re-creation

You could also run an alter statement to change your datatype (as long as all of your data will fit in a varchar(10) column).

ALTER TABLE MyTable
    ALTER COLUMN MyZipCodeColumn VARCHAR(10)

Answer 2

Are you using MS-SQL 2008? Changes that require the table to rebuilt are blocked by default.

Click Tools->Options, then Designers. Uncheck "Prevent saving changes that require table re-creation".

Then you can change your column using the designer.

Screenshots on how to do it:

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx