I had a very frustrating situation the other day, whereupon trying to delete nearly 300 nodes resulted in this pleasant error when trying to empty the Content Recycle Bin in an Umbraco instance.

After reading Njål Gjermundshaug's post on his attempts, I came across a few more SQL errors. So, in an attempt to finally fix it, once and for all, I came up with a SQL cursor.

For those of you that aren't familiar with SQL cursors, they're basically "for-each" loops in SQL. Really nifty, but can be a pain to get right when they get more complex than single-statement commands.

As the greats (well, Hanselman, Torvalds and Key at any rate) say - talk is cheap, show me the code:

DECLARE @TEMPID INT
DECLARE curs CURSOR FOR
SELECT umbracoNode.id FROM umbracoNode, cmsContent WHERE trashed = 1
OPEN curs
FETCH NEXT FROM curs INTO @TEMPID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Delete parents
DELETE FROM cmsPreviewXml WHERE nodeID IN (SELECT id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsContentVersion WHERE ContentId IN (SELECT id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsContentXml WHERE nodeID IN (SELECT id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT Id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE parentID = @TEMPID)
DELETE FROM cmsPreviewXml WHERE nodeID = @TEMPID
DELETE FROM cmsContentVersion WHERE ContentId = @TEMPID
DELETE FROM cmsContentXml WHERE nodeID = @TEMPID
DELETE FROM cmsDocument WHERE nodeID = @TEMPID
DELETE FROM cmsPropertyData WHERE contentNodeId = @TEMPID
DELETE FROM cmsContent WHERE nodeId = @TEMPID
FETCH NEXT FROM curs INTO @TEMPID
END
CLOSE curs
DEALLOCATE curs

So there you have it. You can also change the "WHERE trashed = 1" to "WHERE contentType = whateveryourdoctypeidishere" (replace whateveryourdoctypeidishere with an integer value) to delete all content of a specific type - say, blogposts - oh wait, crap, wasn't meant to do that.