my Code: wipe_commentrange

*sigh*, more comment Spam today — 150 in a few minutes, despite my nice new .Text spam filters (they were all identical).

Unfortunately, I can’t just go into blog_content and delete the range (since the comment count wouldn’t be updated), and I didn’t want to click [Delete], [Yes], [Continue] 150 times, SO: I just fired-up SQL Profiler, reverse-engineered a standard delete call, and slapped together a utility Stored Procedure to loop that call, for big cleanup jobs.

It takes IDs for the first and last comment spam which make it through, and wipes them all out with .Text’s native blog_DeletePost stored proc.  Just paste this into Query Analyzer and run to install:

CREATE PROCEDURE re_wipe_commentrange 
@p_ID int,
@p_lastID int
AS

DECLARE @v_sql varchar(8000)
WHILE @p_ID <= @p_lastID
BEGIN
	SET @v_sql = 'exec blog_DeletePost @ID = ' + cast(@p_ID as varchar(5)) + ', @BlogID = 0'
	EXECUTE(@v_sql)
	SET @p_ID = @p_ID + 1
END
GO

(Note: this is for single user .Text blogs.  Multiple-user blogs will need fiddling with the embedded BlogID parameter.)

Then to use it, call like so in QA: EXEC re_wipe_commentrange 900, 1000.

Might save someone a few minutes (even with writing and posting it here, I think I'd still be clicking delete right now).

Time to consider a way to implement MT-Blacklist...

Bookmark the permalink.

2 Responses to my Code: wipe_commentrange

  1. a tech-centric blog from the left says:
  2. Rob says:

    Yikes, and I already need it again. Not sure why my keyword block didn’t work, but I got 345 &quot;wenzhou&quot; comment spams in 10 minutes this morning.
    <br>
    <br>That stored procs sure is a handy mop tho.

Leave a Reply

Your email address will not be published. Required fields are marked *