3

I right-clicked on a column name in a CREATE TABLE script and selected Refactor | Rename. It renamed the column and the expected entry appears in the refactorlog XML file.

However, when I run a Schema Comparison from my project to my database and then generate the script, the script contains no mention of a refactoring and wants to drop the existing column name and add it with the new name.

I have no _RefactorLog table in this database. The deployment script does not attempt to create one. It's like it is completely ignoring the presence of the refactorlog XML file.

The XML file does start with the expected xmlns:

<?xml version="1.0" encoding="utf-8"?>
<Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
   <Operation Name="Rename Refactor" Key="4c6a080c-0626-4824-9b59-8ecfd491319a" ChangeDateTime="11/18/2013 19:03:13">
    <Property Name="ElementName" Value="[dbo].[Report].[Name]" />
    <Property Name="ElementType" Value="SqlSimpleColumn" />
    <Property Name="ParentElementName" Value="[dbo].[Report]" />
    <Property Name="ParentElementType" Value="SqlTable" />
    <Property Name="NewName" Value="[DisplayName]" />
  </Operation>
</Operations>

I see this in the deployment script:

/*
The column [dbo].[Report].[Name] is being dropped, data loss could occur.

The column [dbo].[Report].[DisplayName] on table [dbo].[Report] must be added,  
but the column has no default value and does not allow NULL values. If the table 
contains data, the ALTER script will not work. To avoid this issue you must 
either: add a default value to the column, mark it as allowing NULL values, or 
enable the generation of smart-defaults as a deployment option.
*/

How can I get this working?

Note: The project file (and all other files) was saved before running the Schema Comparison. The refactorlog file was pre-existing, with about a half-dozen entries from the last time I used the refactor rename feature about a year ago, in addition to the two entries that are new from the current set of changes. It's not that this feature has never worked for me, it's that it no longer works when it used to work.

I am using VS 2012 Premium 11.0.60610.01 Update 3 with SSDT 11.1.31009.1.

Thanks, Mark

Mark Freeman
  • 1,155
  • 3
  • 24
  • 42
  • Have you tried doing a "Publish" to generate the script instead of a schema compare? I think that schema compare doesn't take into account refactoring while publishing will. – Peter Schott Nov 19 '13 at 23:01
  • I've never used Publish. Generating the scripts from Schema Compare used to work, taking the refactor operations into account. – Mark Freeman Nov 19 '13 at 23:24
  • I'd give the publish option a try. I've never had success with just schema comparisons. If that doesn't work, there may be something off with the refactor syntax. – Peter Schott Nov 20 '13 at 03:50
  • Publish is limited in that it doesn't seem to have the ability to exclude specific objects. It also ignores the refactorlog and the publish script also wants to drop the columns whose name have changed and then create the new ones. – Mark Freeman Nov 20 '13 at 15:02
  • If your project is ignoring the refactor log, there's something else wrong. It should definitely be picking that up. If there's nothing else in the refactor log, you may want to try reverting the name to match current systems, renaming the refactor log, then doing a new refactor operation. – Peter Schott Nov 21 '13 at 15:09
  • 1
    The refactorlog file has over a half-dozen previous entries in it. This feature worked fine the last time I used it about a year ago. I decided to just put calls to sp_rename in a script that will run before the script generated from the schema comparison. I just have to move on at this point. Thanks for your suggestions. – Mark Freeman Nov 22 '13 at 15:19
  • @Mark - I am having the same problem, and I would really like to get it straightened out the "right" way. Did you ever find a solution? I've posted a question here: http://stackoverflow.com/questions/27845533/sql-server-data-tools-ignores-refactor-on-schema-compare. – Dan Forbes Jan 08 '15 at 17:00

2 Answers2

2

I am not sure if this is the answer you need, Mark, but my solution to this problem is described here: SQL Server Data Tools Ignores Refactor on Schema Compare. Essentially, SQL Data Tools maintains metadata in the database to ensure that a refactor is only performed once. I'd be interested to know if this was the source of your problem. If not, did you ever find a correct solution?

Community
  • 1
  • 1
Dan Forbes
  • 2,734
  • 3
  • 30
  • 60
  • 1
    I changed jobs back in March 2014 and no longer use this tool. – Mark Freeman Jan 15 '15 at 16:44
  • Hi Dan - I am struggling with the same problem see here https://social.msdn.microsoft.com/Forums/en-US/260a9361-3824-4df7-9e9e-6079c0a4b577/database-project-is-there-a-way-to-rename-a-column-and-get-it-reflected-in-the-actual-database?forum=ssdt – Naomi Oct 21 '16 at 14:09
0

I just ran into this same issue, none of the suggestions worked out. It turns out that - in my case, at least - the Build Action property for the refactor log file was set to "None" - it should be set to "RefactorLog". Once I changed this setting and re-generated the script, all the associated entries in the log were addressed in the generated scripts.

Hope this helps someone.

mklos
  • 21
  • 3