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:
SQL, using GeSHi 1.0.8.8
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
Parsed in 0.014 seconds at 81.61 KB/s
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.