Hi Jack,

Thanks for sharing this code. I am working on a 7.4.3 upgrade to 7.15.5 and came across this error message.

When I use your provided code, it returns 1,110 rows... but I'm not sure what I should now do about those rows...

Should these rows be deleted? Edited in some way? Any further direction you could provide would be helpful.

Thanks in advance :-)

Yes I just removed these items.

(TAKE A FULL DB BACKUP FIRST)

I just ran this:

declare @alias varchar(30) = 'umbracoFile' declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
    --SELECT cmsPropertyData.dataNvarchar, cmsPropertyData.dataNtext, umbracoNode.id, cmsContentVersion.VersionId FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL)
    --SELECT cmsContentVersion.VersionId, count(1) FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) group by cmsContentVersion.VersionId
    delete from cmsPropertyData where versionId in
    (select versionId from
    (select id, versionId, propertytypeid, case when propertytypeid <= 10 then -1 else 1 end as validPropType  from cmsPropertyData where versionid in 
    (SELECT cmsContentVersion.VersionId
    FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid 
    INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId 
    INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id 
    WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN 
    (SELECT umbracoNode.id FROM umbracoNode 
    INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id 
    INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType 
    INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId 
    WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) 
    AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) 
    group by cmsContentVersion.VersionId having count(1) > 1))  t  group by versionId having sum(validproptype) = 0) and propertytypeid in (6,7,8,9,10)

Thanks, Jack :-)

After running the delete I am still getting the errors. Using Ismail's code to look at the specific versionID mentioned in the Install error message, I am seeing other duplicated data...

declare @alias varchar(30) = 'umbracoFile' 
declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'

declare @versionToCheck uniqueidentifier = 'fed7eb58-71c2-452d-aa97-1b452818d62b' --**Update this to match Error msg GUID

SELECT cmsPropertyData.id, cmsPropertyData.dataNvarchar, 
cmsPropertyData.dataNtext, 
umbracoNode.id, 
cmsContentVersion.VersionId,cmsPropertyData.propertytypeid
FROM cmsPropertyData INNER JOIN cmsPropertyType 
ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode 
ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion 
ON cmsContentVersion.ContentId = umbracoNode.id 
WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) 
AND cmsContentVersion.VersionId=@versionToCheck

Interestingly, I am getting duplicate data with different property ids...

All I can figure is that at some point the original developers changed the property type or something, but the old data is still present.

I have been able to deal with them individually and in a rather manual fashion by grabbing the first "id" value and running

DELETE FROM cmsPropertyData
WHERE id = XXX;

But, it's been pretty painful [sigh]...

Well, just in case someone else has a similar issue... I finally was able to get these "bulk" deleted by using Excel for help.

First, I ran this query:

declare @alias varchar(30) = 'umbracoFile' 
declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'

SELECT cmsPropertyData.id, cmsPropertyData.dataNvarchar, 
cmsPropertyData.dataNtext, 
umbracoNode.id, 
cmsContentVersion.VersionId,cmsPropertyData.propertytypeid
FROM cmsPropertyData INNER JOIN cmsPropertyType 
ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode 
ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion 
ON cmsContentVersion.ContentId = umbracoNode.id 
WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) 
ORDER BY umbracoNode.id, 
cmsContentVersion.VersionId, 
cmsPropertyData.id

Then, I took the results and pasted into Excel.

I added a column to check if the row matched the next row (to get all the duplicates) with the cell formula:

=AND(E2=E3,F2=F3)

(Results in TRUE or FALSE on each row)

And then another column to copy the 'id' of the TRUE rows:

=IF(H2=TRUE,B2,"")

I then copied the column and pasted it as VALUES, resulting in a full list of the IDs of rows which needed to be deleted. The final list of IDs was then formatted into a comma-separated list and then this SQL was run, using the list of over 500 ids grabbed from excel:

DELETE
FROM cmsPropertyData
WHERE id in (1962,1967,2008,2487,4409,4425,4433,...)

I was finally able to get the Upgrade wizard to complete after this. :-)

Heather Floyd, thank you so much!

I recently ran into this same issue with an Umbraco Cloud upgrade to version 7.9.7 and your post saved me!

Thankfully I only had 2 duplicates, but still, I wouldn't have known where to start if it hadn't been for this thread and your post.

I'm glad it helped you, Tim 😊