dacpacsqlproj

No column in target database, but getting "The schema update is terminating because data loss might occur"

I had been getting the error when publishing a SQL project.

I was testing on a database made from the production schema with some test data.

When I looked at the error from the Data Tools Operations tab in Visual Studio, I saw this:

/*
The column [dbo].[MyTableName].[UnexpectedColumnName] is being dropped, data loss could occur.
*/
IF EXISTS (select top 1 1 from [dbo].[MyTableName])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

However, the UnexpectedColumnName column name has not been in that table for quite a long time.

The question was how why that SQL was being generated.

Solution

  • So it turns out the refactor log had something really old in it. I asked the publish step to generate the script rather than publish immediately and searched for UnexpectedColumnName:

    GO
PRINT N'The following operation was generated from a refactoring log file 69ad6ffa-f53e-4302-a9af-a6f6907e4eec, 5879552a-9f2d-439c-82c8-352bbee08bb2';
PRINT N'Rename [dbo].[MyTableName].[ArbitraryRealColumnName] to UnexpectedColumnName';

GO
EXECUTE sp_rename @objname = N'[dbo].[MyTableName].[ArbitraryRealColumnName]', @newname = N'UnexpectedColumnName', @objtype = N'COLUMN';

    I then looked into the refactoring log and saw this matching the GUID:

      <Operation Name="Rename Refactor" Key="5879552a-9f2d-439c-82c8-352bbee08bb2" ChangeDateTime="02/13/2020 20:07:08">
    <Property Name="ElementName" Value="[dbo].[MyTableName].[NotSureWhatThisIs]" />
    <Property Name="ElementType" Value="SqlSimpleColumn" />
    <Property Name="ParentElementName" Value="[dbo].[MyTableName]" />
    <Property Name="ParentElementType" Value="SqlTable" />
    <Property Name="NewName" Value="UnexpectedColumnName" />
  </Operation>

    Because we don't use those refactoring log features, I just deleted the whole refactoring log, and everything worked. I'd imagine you could also just delete the errant rename.