Community Server on my mind

Correction of one of my old posts

Previously on J-O Erikssons blog: A long, long time ago. At least in Internet Time. At J-O Eriksson's old blog there was an article about a solution to handle sending Newsletters to your Community Server users with the help of CS Roles.

As time went by, new articles was written, other areas of Community Server was examined, and the solution was forgotten. Until one day when J-O needed to use this solution on one of his sites. He discovered that it no longer worked! It was broken, kaputt!

After much thought and scratching his head, J-O realized that the DB Schema had been slightly changed over time, and that his T-SQL statement therefore also needed to be changed. So, by the pale light of his computer screen, he started up his SQL Server Management Studio, and started to write a new script to get it to work again.

He wrote, tested, made changes, and tested again, scratched his head, drank some coffe, made more changes. And finally, the following all new T-SQL script was written and worked again. Now J-O could finally go to sleep after a hard days work, and hopefully dream some sweet dreams of the upcoming CS 2007.

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

INSERT INTO #NewRole (UserID)

SELECT DISTINCT aspnet_Users.UserID
    FROM aspnet_Users INNER JOIN aspnet_UsersInRoles
    ON aspnet_Users.UserID = aspnet_UsersInRoles.UserId
    WHERE ApplicationID = 'your ApplicationID here'
    AND aspnet_Users.UserID <> 'your Anonymous UserID here'
    AND aspnet_UsersInRoles.RoleID <> 'your NewsLetter RoleID here'

UPDATE #NewRole
    SET RoleID = 'your NewsLetter RoleID here'

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

DROP TABLE #NewRole

For further information what this is good for, see the original article at my old blog.