Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForumForumCopying module settings, groups, forums, and posts to a new portal (SQL)Copying module settings, groups, forums, and posts to a new portal (SQL)
Previous
 
Next
New Post
5/21/2008 1:25 AM
 

I run a series of cell phone sites, all of which are based on the same structure. Since the dotnetnuke template functionality doesn't carry over forum module content, I wrote the following SQL script to copy the settings, groups, forums and certain posts from an existing portal. It works great for me so please feel free to use some or all of the script, however I make no guarantees and you do so at your own risk.

NOTE: You'll need to set the @OldPortalID variable's value on line 3 and PostID parameter for each post you want to copy. The script assumes the newest portal (with the highest PortalID) is the one you want to copy everything to.

DECLARE @MODULEID INT, @PORTALID INT, @PORTALNAME NVARCHAR(50), @OLDPORTALID INT, @OLDPORTALNAME NVARCHAR(50), @OLDMODULEID INT, @CURRENTDATE DATETIME
SET @CURRENTDATE = GETDATE()
SET @OLDPORTALID = 9   -- Change this to the PortalID of the portal you wish to copy from
SELECT @OLDMODULEID = FROM Modules WHERE (ModuleTitle LIKE '%FORUM%') AND PORTALID = @OLDPORTALID
SELECT @OLDPORTALNAME = PORTALNAME FROM PORTALS WHERE PORTALID = @OLDPORTALID
SELECT @MODULEID = MAX(MODULEID) FROM Modules WHERE (ModuleTitle LIKE '%FORUM%')
SELECT @PORTALID = MAX(PORTALID) FROM Portals
SELECT @PORTALNAME = PORTALNAME FROM PORTALS WHERE PORTALID = @PORTALID

------------------------------------------------------------------------------------------------
-- COPY SETTINGS
DELETE FROM MODULESETTINGS WHERE MODULEID = @MODULEID
INSERT INTO [ModuleSettings]
           ([ModuleID]
           ,[SettingName]
           ,[SettingValue])
SELECT @MODULEID
      ,[SettingName]
      ,REPLACE([SettingValue], @OLDPORTALNAME, @PORTALNAME)
FROM [ModuleSettings]
WHERE MODULEID = @OLDMODULEID

------------------------------------------------------------------------------------------------
-- COPY GROUPS
INSERT INTO [Forum_Groups]
           ([Name]
           ,[PortalID]
           ,[ModuleID]
           ,[SortOrder]
           ,[CreatedDate]
           ,[CreatedByUser]
           ,[UpdatedByUser]
           ,[UpdatedDate])
SELECT [Name]
      ,@PORTALID
      ,@MODULEID
      ,[SortOrder]
      ,GETDATE() AS [CreatedDate]
      ,[CreatedByUser]
      ,[UpdatedByUser]
      ,GETDATE() AS [UpdatedDate]
FROM [Forum_Groups]
WHERE PORTALID = @OLDPORTALID
AND [Name] NOT IN (SELECT [Name] FROM FORUM_GROUPS WHERE PORTALID = @PORTALID)

------------------------------------------------------------------------------------------------
-- COPY FORUMS
INSERT INTO [Forum_Forums]
           ([GroupID]
           ,[IsActive]
           ,[ParentID]
           ,[Name]
           ,[Description]
           ,[CreatedDate]
           ,[CreatedByUser]
           ,[UpdatedByUser]
           ,[UpdatedDate]
           ,[IsModerated]
           ,[SortOrder]
           ,[TotalPosts]
           ,[TotalThreads]
           ,[EnablePostStatistics]
           ,[MostRecentPostID]
           ,[MostRecentThreadID]
           ,[MostRecentPostAuthorID]
           ,[MostRecentPostDate]
           ,[PostsToModerate]
           ,[ForumType]
           ,[IsIntegrated]
           ,[IntegratedModuleID]
           ,[AuthorizedRoles]
           ,[AuthorizedEditRoles]
           ,[PublicView]
           ,[PublicPosting]
           ,[EnableForumsThreadStatus]
           ,[EnableForumsRating]
           ,[ForumLink]
           ,[ForumBehavior]
           ,[AllowPolls])
SELECT G2.[GroupID]
      ,F1.[IsActive]
      ,F1.[ParentID]
      ,F1.[Name]
      ,F1.[Description]
      ,GETDATE() AS [CreatedDate]
      ,F1.[CreatedByUser]
      ,F1.[UpdatedByUser]
      ,GETDATE() AS [UpdatedDate]
      ,F1.[IsModerated]
      ,F1.[SortOrder]
      ,0 AS [TotalPosts]
      ,0 AS [TotalThreads]
      ,F1.[EnablePostStatistics]
      ,NULL AS [MostRecentPostID]
      ,NULL AS [MostRecentThreadID]
      ,NULL AS [MostRecentPostAuthorID]
      ,NULL AS [MostRecentPostDate]
      ,0 AS [PostsToModerate]
      ,F1.[ForumType]
      ,F1.[IsIntegrated]
      ,F1.[IntegratedModuleID]
      ,F1.[AuthorizedRoles]
      ,F1.[AuthorizedEditRoles]
      ,F1.[PublicView]
      ,F1.[PublicPosting]
      ,F1.[EnableForumsThreadStatus]
      ,F1.[EnableForumsRating]
      ,F1.[ForumLink]
      ,F1.[ForumBehavior]
      ,F1.[AllowPolls]
FROM [Forum_Forums] F1
INNER JOIN FORUM_GROUPS G1
ON F1.GROUPID = G1.GROUPID
AND G1.PORTALID = @OLDPORTALID
INNER JOIN FORUM_GROUPS G2
ON G1.NAME = G2.NAME
AND G2.PORTALID = @PORTALID
LEFT OUTER JOIN FORUM_FORUMS F2
ON G2.GROUPID = F2.GROUPID
AND F1.[NAME] = F2.[NAME]
WHERE F2.FORUMID IS NULL

------------------------------------------------------------------------------------------------
-- ADD POSTS
DECLARE @RC int, @POSTID INT, @ParentPostID int, @ForumID int, @UserID int, @Notify bit, @Subject nvarchar(255), @Body NVARCHAR(MAX), @IsPinned bit
DECLARE @IsClosed bit, @ObjectID int, @FileAttachmentURL nvarchar(50), @PollID int, @NEWFORUMID INT

-- Duplicate the code below for each post you wish to copy into the new portal

-- NOTE: Be sure to change the PostID for each one
SELECT @POSTID = P.POSTID, @FORUMID = T.FORUMID, @USERID = P.USERID, @NOTIFY = P.NOTIFY, @SUBJECT = P.SUBJECT, @BODY = P.BODY, @ISPINNED = T.ISPINNED
    , @ISCLOSED = P.ISCLOSED, @OBJECTID = T.OBJECTID, @FILEATTACHMENTURL = P.FILEATTACHMENTURL, @POLLID = T.POLLID
FROM FORUM_POSTS P INNER JOIN FORUM_THREADS T ON P.THREADID = T.THREADID WHERE P.POSTID = 0000 -- Change this to the PostID <--
SELECT @NEWFORUMID = F2.FORUMID FROM FORUM_FORUMS F1
INNER JOIN FORUM_GROUPS G1 ON F1.GROUPID = G1.GROUPID
INNER JOIN FORUM_GROUPS G2 ON G1.[NAME] = G2.[NAME] AND G2.PORTALID = @PORTALID
INNER JOIN FORUM_FORUMS F2 ON G2.GROUPID = F2.GROUPID AND F1.[NAME] = F2.[NAME]
WHERE F1.FORUMID = @FORUMID
EXECUTE @RC = [Forum_Post_Add] 0, @NEWForumID, @UserID, '127.0.0.1', @Notify, @Subject, @Body, @IsPinned, @CURRENTDATE, @IsClosed, @ObjectID
    , @FileAttachmentURL, @PortalID, 0, @PollID

 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsForumForumCopying module settings, groups, forums, and posts to a new portal (SQL)Copying module settings, groups, forums, and posts to a new portal (SQL)


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out