Some time during summer, I got lots and lots of referral spam. I tried adding a trigger to my SQL statements, but that didn't work out too well. And then I didn't bother with them anymore, as they stopped coming in. But today I decided to do some cleaning up, and created a T-SQL script that will loop through numbers x to y, and execute the built in stored procedure blog_DeletePost.
The blog_DeletePost stored procedure takes two parameters: ID and BlogID. The ID is the ID of the row you want to delete. If your spam rows have ID 409 to 489, set x=409 and y=490. If there are several batches of spam rows, you'll just have to run the script over and over with the right intervals.
You have to have access to the SQL server running the blog database to do it this way. I think it should have been a feature in dotText, but as it isn't, I've done it my way.
declare @Counter int
set @Counter = x
while @Counter < y
begin
--print "Counter: " + cast(@Counter as char)
exec blog_DeletePost @Counter, 4
set @Counter = @Counter + 1
end
Remove the --s in front of print to see the rows being deleted. If you want to try it out first, just move the --s from in front of Print to in front of Exec. I got rid of something like 500 rows of spam in just a few seconds.