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

Question

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:

• Compare Server1 database to Schema, and update the Schema with Server1 changes
• Compare Schema to Server2 database, and update changes from the Schema to the database

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.

Cappuccino

If you do a compare of the project to the database, do any other properties show up as different? Sometimes there's a subtle difference that you don't see, but is enough to trigger a drop/recreate. Also, check the "refactorlog" file to see if there's an entry in there to rename the column. If not, it may need to be created. Easiest way is to rename to something, then name back - remove the extra rename and you should be good. – Mawkish

Answer 1

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.

Froze

can rename column can be propagated to the next server using publish script – Slingshot

Yes it can be. Just use publish scripts or deploy from dacpac on another server. – Froze

Thanks scar. I have a table Employee (ID, FirstName, MiddleInitial, LastName, Age, DOJ). I have included this into the EDMX and published across Dev, UAT and Prod environment. Now, we are changing the field MiddleInitial to MiddleName. we renamed the column in EDMX. But, when we publish the db, the MiddleInital values are not copied to MiddleName. Not sure why it is not doing that and any corrections or solution please ? – Slingshot

I would try to generate publish SQL script and see what is hapening inside. – Froze

Yes, I tried that and it created a temp table with some name with a new column name (ID, FirstName, MiddleName, LastName, Age, DOJ) and then it is selecting ID, FirstName, LastName, Age, DOJ column values from Employee Table and Inserts into the new temp table. And finally, it drop the employee table and rename the temp table to Employee table. Any help – Slingshot

Sorry, I don't know what is wrong there. You can try to create new question with code attached. – Froze

Thank you and I'll try to do that – Slingshot

Answer 2

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!

Godrich

It seems it must be obvious, so I'm afraid I'm completely lost. I'm using SSDT in VS2012&13. I'm on the comparison window. Right click only have the "Include/Exclude" options. There is a button bar at the top of the comparison window, with Compare, Stop, Update, etc. buttons. And I don't find any option related to SSTD on the main VS menu. If I right click on the table definition script file I only see Refactor -> [Expand wildcards, Fully qualify names]. I'm a little blind or its a little hidden ;) Please, include the explanation in the answer itself. Thank you very much for the quick answer – Cappuccino

I upvoted this answer, because it's correct, but, as my comment shows there was some info to make it compeltelyunderstandable, so I chose the other as the solution. Thank you anyway. – Cappuccino

where did you find refactor rename – Nahshu

@Cappuccino can you let me know how you did it finally – Nahshu

If you right click the column in the 'T-SQL' window after you've opened the .sql file you should see 4 refactor options, one of them 'Rename...'. – Marbles

Answer 3

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.

Theine