CODEWITHSUNDEEP
sql-server-2012sql-server-data-tools
JotaBe
JotaBe

Reputation: 39055

Instead of column rename the project does column drop and column add

I'm using SSDT to keep 2 databases synchronized.

To do so, I have a database in Server1, a database in Server2 and an Schema in my project.

I always make changes in Server1 database, and apply those changes in Server2 database. To do so, I use this workflow:

This usually works fine, but I've found a problem the last time I've renames columns in a table.

Usually, if I rename columns in a table, the change is dectected as a column rename, so, when I compare the Server1 to the Schema, the column renames are correctly detected, and I can safely finish my work flow.

However, the last time that I have renamed columns in a table in Server1, when comparing it to the Schema, instead of detecting the change as a column rename, it has detected the change as a drop column (with old name) and create column (with new name). Obviously, if I apply those changes in Server2 database I'll lose all the data in the renamed column.

Is there any reason for this behaviour in SSDT? Can I instruct SSDT to understand that this is a column rename?

I know how to do it by hand, but I'd prefer to avoid this problem in SSDT, or be able to solve it, if it appears again in the future.

Upvotes: 17

Views: 8050

Answers (3)

Sean McCafferty
Sean McCafferty

Reputation: 256

I know this is an old question but just to clarify for those that are still finding this and can't figure out how to do the refactor steps.

While in the design view of Visual Studio, click on the column name that you want to change then click the "SQL" option in the top Visual studio menu. Then click "Refactor" > "Rename": Image with the menu option highlighted

Next you can right click on the project itself within the Solution Explorer window and choose the "Schema Compare..." option.

Upvotes: 3

scar80
scar80

Reputation: 1702

I think there is some misunderstanding. You have done a column rename on a real database and now you want to do schema compare to propagate column rename from the database to the SSDT project. It won't work this way as SSDT can't detect that the column is actually renamed.

The right scenario is to rename a column in SSDT first (right click on the column -> Refactor -> Rename. A refactorlog file will be created - you must not delete it.). Then do a schema compare between your project and a target database. The change will be propagated to the server as column rename.

Upvotes: 21

Ed Elliott
Ed Elliott

Reputation: 6856

Did you use the refactor-->rename menu option? That is how to get it included, if you did and it didn't work then I would file a bug on connect.

To manually put it in either rename it back by hand then use the refactor menu or check in the refactorlog.xml and it is pretty easy to add an entry manually.

Let us know what happened/you decide to do!

Upvotes: 6

Related Questions