Finding the newsletter solution

For Community Server, there doesn't seem to be a feature for sending a newsletter to only subscribed members. Here is a simple solution from JO Eriksson on designing a simple opt out newsletter scheme for Community Server installation.

Now the only thing while i was implementing it for Community Server 2.1 (Standard Edition) was that the Stored procedure (meant for migrating existing users into the role) produced in the post wasn't working for me. Appears there was a schema change in CS 2.1. I have modified the SP to work with CS 2.1 as follows:

 

CREATE TABLE #NewRole (
[UserId] [uniqueidentifier] NOT NULL ,
[RoleId] [uniqueidentifier]
)

INSERT INTO #NewRole (UserID)
select userid from aspnet_Users
where userid not in
(
select aspnet_users.userid from aspnet_UsersInRoles inner join aspnet_users on aspnet_Users.UserId = aspnet_UsersInRoles.UserId
where aspnet_UsersInRoles.RoleId = 'bb497bc8-d18a-4244-8b1b-8aaa80dc974d'
)

UPDATE #NewRole
SET RoleID = 'bb497bc8-d18a-4244-8b1b-8aaa80dc974d'

INSERT INTO aspnet_UsersInRoles (UserID, RoleID)
SELECT UserID, RoleID FROM #NewRole

DROP TABLE #NewRole

 

Technorati tags: , ,

Comments

Anonymous said…
Hmmm... I wonder if the need for the SQL Script change is due to that you've also gone from ASP .NET 1.1 to ASP .NET 2.0.

Popular posts from this blog

Community Star Discussions and Future

Notes from an Internet Marketing Seminar

Solving the multi-platform challenge in mobile devices