Changing message and notification email frequency for all users in a portal

Overview

Administrators can follow the steps below to change how often users receive emails regarding site notifications or private messages. Note that the script provided will change these settings for all users in a specific portal. Users can change their settings individually in their account settings.

Environment: Version 6.2 or later

Step-by-Step Guide

1. Access the SQL Console or connect to your server with SQL Server Management Studio.
2. Find your portal's ID number using steps 1 and 2 of the Default Worfklow article.
3. Modify the values for PortalID, MessagesEmailFrequency, and NotificationsEmailFrequency as directed in the attached script. You can also copy the script from the text below.
   
   Note: the integer values for MessagesEmailFrequency and NotificationsEmailFrequency correspond to the following settings:
   • 0 = instant
   • 1 = hourly
   • 2 = weekly
   • 3 = monthly
     
     
4. Run the modified script.

Appendix: Script

The script is copied below. Please note that this script requires your site's PortalID to work, and you must MessagesEmailFrequency and NotificationsEmailFrequency to one of the valid integer settings noted above.

  /*This script will update all the email and notification frequencies for all users on a specific portal. Written by Tony Lee*/
  DECLARE @PortalID AS INT = 0 /*Set PortalID*/
  DECLARE @UserID AS INT
  DECLARE @MessagesEmailFrequency AS INT = 0 /*Set Frequency*/
  DECLARE @NotificationsEmailFrequency AS INT = 0 /*Set Frequency*/
  
  DECLARE @EmailCursor AS CURSOR;

        SET @EmailCursor = CURSOR FOR
                SELECT [UserID]  
                FROM UserPortals
                WHERE PortalID = 0; /*Set PortalID*/

        OPEN @EmailCursor;
  
        FETCH NEXT FROM @EmailCursor INTO @UserID;
  
        WHILE @@FETCH_STATUS = 0
        BEGIN
                IF NOT EXISTS (SELECT * FROM [CoreMessaging_UserPreferences] WHERE UserId = @UserID and PortalId = @PortalID)
                        BEGIN
                                   INSERT INTO [dbo].[CoreMessaging_UserPreferences]
                                                                   ([PortalId]
                                                                        ,[UserId]
                                                                        ,[MessagesEmailFrequency]
                                                                        ,[NotificationsEmailFrequency])
                                                         VALUES
                                                                   (@PortalID
                                                                   ,@UserID
                                                                   ,@MessagesEmailFrequency
                                                                   ,@NotificationsEmailFrequency)
                        END
                ELSE
                        BEGIN
                                UPDATE [CoreMessaging_UserPreferences] 
                                SET MessagesEmailFrequency = @MessagesEmailFrequency
                                , [NotificationsEmailFrequency] = @NotificationsEmailFrequency 
                                WHERE [UserId] = @UserID AND [PortalId] = @PortalID
                        END
                                
                FETCH NEXT FROM @EmailCursor INTO @UserID;
        END

CLOSE @EmailCursor;

DEALLOCATE @EmailCursor;