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;

 

Attachments

Comments

0 comments

Please sign in to leave a comment.