Chris Hammond is

a father, husband, developer, geek, car guy. The Director of Training for DotNetNuke Corporation. To learn more about Chris check out the about me page.

I use Strava to track my bicycle rides. Below you can see my recent activity.

Deleting unused blogs and photo galleries in CommunityServer.

Last Updated Wednesday, January 23, 2008 4:51 PM

Printer Friendly Version

Email A Friend

I decided to remove some unused blogs and photo galleries from a few of my CommunityServer sites today. Here's some ROUGH SQL code to do it.

It worked for me, but I make no guarantees how it will work for you! Backup your database before performing any type of maintenance.

Be sure to sub in your UserId where I note below with the ##. I run the commented out code first at the top, this creates a quick table, after doing my testing I commit the changes then drop the table I had created.

I don't claim this code is great, but it got the job done :D

create table temp_deletesections

(

SectionId int,

Processed int default 0

)

begin tran

tran

--rollback

declare @rowsAdded int

insert into temp_deletesections

@rowsAdded int

insert into temp_deletesections

into temp_deletesections

select sectionid, 0 from cs_sections

sectionid, 0 from cs_sections

where totalposts <1

totalposts <1

set @rowsAdded = @@rowcount

@rowsAdded = @@rowcount

while @rowsAdded > 0

begin

declare @sectionDeleteId int

select top 1 @sectionDeleteId = sectionId from temp_deletesections where processed=0

@rowsAdded > 0

begin

declare @sectionDeleteId int

select top 1 @sectionDeleteId = sectionId from temp_deletesections where processed=0

begin

declare @sectionDeleteId int

select top 1 @sectionDeleteId = sectionId from temp_deletesections where processed=0

@sectionDeleteId int

select top 1 @sectionDeleteId = sectionId from temp_deletesections where processed=0

top 1 @sectionDeleteId = sectionId from temp_deletesections where processed=0

exec dbo.cs_Section_CreateUpdateDelete @SectionID=@sectionDeleteId,@DeleteForum=1,@SettingsID=1000,@UserID=##

dbo.cs_Section_CreateUpdateDelete @SectionID=@sectionDeleteId,@DeleteForum=1,@SettingsID=1000,@UserID=##

update temp_deletesections set Processed = 1 where Processed = 0 and sectionId = @sectionDeleteId

temp_deletesections set Processed = 1 where Processed = 0 and sectionId = @sectionDeleteId

set @rowsAdded = @rowsAdded-1

@rowsAdded = @rowsAdded-1

end

--commit

--drop table temp_deletesections

Posted from weblogs.asp.net/christoc

Tags: asp.net, Community Server, SQL Server