We have a lot of administrative tools for SQLServerCentral that are available online. Most of the work I do can be done from the web, which is cool. It means that I can manage things from anyplace, and any computer. That’s been handy at times when I’ve been away from my desk, even at conferences, and needed to get things done.
However we don’t have a tool for everything. There are some one-off issues, like awarding points back for the QOD, that I rarely do. Despite the fact that it seems to me I’m doing this often, it’s probably 10 times a year, and so it’s not really worth building a tool. Instead it’s easier to just hit the database and make a correction.
I answer a lot of threads on the site, posting 300+ times a month, and I’ve set my notifications to email as a default. It’s a good way for me to see when someone responds, and go check the thread to see if I need to provide more info or add another comment. However that means I’m subscribed to a lot of threads. In fact, here was my list the other day, after I’d pruned out a couple of pages (I think 20-25 a page).
Everyone has this page in their forum Control Panel, at the bottom where there’s a “topic subscriptions” link. There’s also a forum subscriptions link for those that subscribe with that method. I don’t do forums, only topics. Next to each subscription there’s that checkbox, and then at the bottom a button to “unsubscribe” from all the items checked on that page. It’s not a big deal to be subscribed to old threads, but it does become a hassle when I want to search one out. For most people they might have a dozen, but for a few of us, we have thousands. So I try to trim them periodically.
This is the bottom of the page, where there’s an extra check box. I would assume that checking that one would check all boxes on the page, and if that worked, it would be easy to remove a few hundred subscriptions a day and keep things manageable.
It doesn’t.
In fact, as near as I can tell, it doesn’t do anything, which means that I must manually check all boxes on a page, which is slow and error prone with a mouse. Then I click the unsubscribe button, and repeat, 70 times in this case, which isn’t something I want to do. Or think I should do.
The solution, for me, is to back door this. It won’t work for others, but this is a case, where it makes sense to me. I connect to the database servers, and load up a script. I built the script by poking around in the tables, looking for something that seemed like it stored this data. In this case, the table instantforum_topicsubscriptions seemed likely, and digging through it, I found my user had 1560 rows in there, which matched up with the first image above. We bought the forum code, so we don’t have a good ER diagram of Data Dictionary of where things go. I’ve poked around over the years to find some things, and in this case, wrote a simple delete.
DELETE s
from instantforum_topicsubscriptions s
inner join instantforum_topics t
on s.topicid = t.topicid
where t.lastposteddate > dateadd( d, –90, getdate())
This kills any threads which haven’t seen a post in 90 days, and that’s pretty generous. I might cut it down to 45 days, but for now I’ll stick with 90. That reduces me down to a more manageable level. It isn’t worth engaging a developer for a couple days to build me a better tool since it’s very few people that need to do something like this. It’s also not worth complaining to instantASP since how many people on a typical board have that many subscriptions? I bet even most active members have a few hundred at most. Not necessarily trying to develop a tool to deal with. As you can see, it worked.
Wow, I’ve been busy. Might need to rethink that 90 day thing.
Voice of the DBA 