Mass Delete Anonymous Posts on Community Server
Edge UG has always fought a losing battle against spammers. So I decided to take action today and clear our database of all anonymous posts. This may feel like a little broad-sworded but our site is unique where all posts are from registered users. I thought I’d share the script I wrote (with help from Johan Barnard). Feel free to modify it to be a little smarter in terms of how it selects the posts to delete but the useful part of this script is that it does use community server’s built in SPs for post deletion.
As a side note, the hardcoded @DeletedBy is my user id which I did as this was a single use script.
DECLARE @sectionId int, @settingsId int, @postId int
DECLARE post_cursor CURSOR FOR
SELECT SectionId, SettingsId, PostID
FROM cs_Posts
WHERE PostAuthor = ''
OPEN post_cursor
FETCH NEXT FROM post_cursor
INTO @sectionId, @settingsId, @postId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @message int
SELECT @message = @postId
PRINT @message
exec [dbo].[cs_Post_Delete]
@SectionId = @sectionId,
@SettingsId = @settingsId,
@PostID = @postId,
@ResetStatistics = 1,
@DeletedBy = 2102,
@Reason = 'auto spam delete'
FETCH NEXT FROM post_cursor
INTO @sectionId, @settingsId, @postId
END
CLOSE post_cursor
DEALLOCATE post_cursor