We have encountered very specific problem when we have pushed changes in template field type via Unicorn.

Problem Definition

Change was pretty simple. From Shared field type, we have changed it to Versioned field. Problem was that the change was propagated via Unicorn to higher environments and therefore content or items which where based on that item were in inconsistent state.

Template field type was already changed via Unicorn Sync but field values where left in SharedFields table and they were not moved to VersionedFields table.

This is not Unicorn issue or any other tooling issue. It’s issue of how Sitecore is propagating those template field type changes. If you do them via UI e.g. Content Editor, proper event is triggered and data is moved between tables correctly. If you do the change from “outside”, event is not triggered.

Resolution

We have created ticket to Sitecore Support and although the issue was clasiffied as not in Sitecore but in Unicorn they have headed us to this KB article -> Installing templates with a modified field sharing type may cause database corruption.

If you read that KB article thoroughly, you will see warning that the process might cause data loss. Also for Sitecore 10.2 that we were using we would need to modify the code as some of the things have been already changed between version for example item.BeginEdit(); -> item.Editing.BeginEdit(); Also I didn’t like that for one time fix we need to deploy dll, aspx and config file and run some modified code.

Therefore, I have come up with different approach!

I have prepared series of SQL scripts to do the job in SQL db directly…

  1. First script will get you list of discrepancies for that particular field (change field id accordingly ;-))

    SELECT DISTINCT vf.ItemId, vf.Language, vf.Version, sf.Value FROM dbo.VersionedFields vf INNER JOIN dbo.SharedFields sf ON sf.ItemId = vf.ItemId where sf.FieldId = '7D8B8B1F-474D-40B4-B7D8-A9E21FF8A05B'
  2. Second script will insert values from SharedFields table to correct VersionedFields table

    WITH FieldValues (ItemId, Language, Version, FieldId, Value) AS
    (SELECT DISTINCT vf.ItemId, vf.Language, vf.Version, sf.FieldId, sf.Value FROM dbo.VersionedFields vf INNER JOIN dbo.SharedFields sf ON sf.ItemId = vf.ItemId where sf.FieldId = '7D8B8B1F-474D-40B4-B7D8-A9E21FF8A05B')
    INSERT INTO [dbo].[VersionedFields]            
    ([Id],[ItemId], [Language] ,[Version] ,[FieldId] ,[Value] ,[Created] ,[Updated]) SELECT NEWID(), ItemId, Language, Version, FieldId, Value, GETDATE(), GETDATE() FROM FieldValues
  3. Third script will remove old values from SharedFields table

    DELETE
    FROM [dbo].[SharedFields]
    WHERE ItemId IN
    (SELECT DISTINCT vf.ItemId
    FROM dbo.VersionedFields vf
    INNER JOIN dbo.SharedFields sf ON sf.ItemId = vf.ItemId
    where sf.FieldId = '7D8B8B1F-474D-40B4-B7D8-A9E21FF8A05B'    )

That’s it. I believe with small modification scripts will work also for different scenarios like Shared to Unversioned field type…

Kudos to Paul Stupka for optimizing my third script 😉

Happy Sitecoring!