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

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

(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.

Leave a Reply

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