Script 1 is called ajmReadNTForums:
-- Description: Migrate Forums
-- =============================================
ALTER PROCEDURE ajmreadNTForums
-- Add the parameters for the stored procedure here
AS
DECLARE @Counter int,
@ParentPostid int,
@Forumid int,
@Userid int,
@RemoteAddr varchar(200),
@Subject nvarchar(150),
@Body varchar,
@Pinned bit,
@DateAdded datetime
DECLARE MyCursor CURSOR
GLOBAL
FOR
SELECT ParentPostID, ForumID, UserID, IPAddress, Subject, Body, Pinned, DateAdded
FROM NTForums_Posts
SELECT @Counter=0
OPEN MyCursor
WHILE (@Counter <10) AND (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM MyCursor INTO @ParentPostID, @ForumID, @UserID, @RemoteAddr, @Subject, @Body, @Pinned, @DateAdded
PRINT CONVERT(varchar,@ParentPostID) + CONVERT(varchar,@ForumID) + CONVERT(varchar,@UserID) + @RemoteAddr + CONVERT(varchar,0)+ CONVERT(varchar,@Subject)+ CONVERT(varchar,@Body)+ CONVERT(varchar,@Pinned)+ CONVERT(varchar,@DateAdded)
-- CONVERT (varchar,@Postid) + " ForumID " + CONVERT(varchar,@ForumID)
SELECT @Counter = @Counter +1
/*
we write to ajmForumPostAdd: @ParentPostID, @ForumID, @UserID, @RemoteAddr,
0, @Subject, @Body, @Pinned, @DateAdded,
0, 0, "", 0, 0.
*/
EXECUTE ajmForumPostAdd @ParentPostID, @ForumID, @UserID, @RemoteAddr, 0, @Subject, @Body, @Pinned, @DateAdded, 0, 0, '', 0, 0, 0, @DateAdded
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
Script 2 is called ajmForumPostAd, it is called by Script 1:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [ajm_Forum_Post_Add]
(
@ParentPostID INT,
@ForumID INT,
@UserID INT,
@RemoteAddr NVARCHAR(100),
@Notify BIT,
@Subject NVARCHAR(255),
@Body NTEXT,
@IsPinned BIT,
@PinnedDate DATETIME,
@IsClosed BIT,
@ObjectID INT,
@FileAttachmentURL NVARCHAR(50),
@PortalID INT,
@ThreadIconID INT,
@PollID INT,
@DateAdded DATETIME)
AS
DECLARE @@PostID INT
DECLARE @@MaxTreeSortOrder INT
DECLARE @@MaxFlatSortOrder INT
DECLARE @@ParentLevel INT
DECLARE @@ThreadID INT
DECLARE @@ParentTreeSortOrder INT
DECLARE @@NextTreeSortOrder INT
DECLARE @@CreatedDate DATETIME
DECLARE @@ApprovedPost BIT
DECLARE @@ModeratedForum BIT
DECLARE @@GroupID INT
DECLARE @@DateApproved DATETIME
SELECT @@CreatedDate = @DateAdded
-- Determine if this forum is moderated
SELECT @@ModeratedForum = IsModerated,
@@GroupID = GroupID
FROM dbo.Forum_Forums
WHERE ForumID = @ForumID
IF @@ModeratedForum = 0
BEGIN
SELECT @@ApprovedPost = 1
END
ELSE
BEGIN -- with moderated forum, if this user is trusted or user is a moderator the post will be approved
--SET @ApprovedPost = (SELECT IsTrusted FROM dbo.Forum_Users WHERE UserID = @UserID)
SET @@ApprovedPost = ISNULL(( SELECT NULLIF(COUNT(DISTINCT UserID),
0)
FROM dbo.Forum_Moderators
WHERE UserID = @UserID
), ( SELECT IsTrusted
FROM dbo.Forum_Users
WHERE UserID = @UserID
AND PortalID = @PortalID
))
END
IF @@ApprovedPost = 1
BEGIN
SET @@DateApproved = GETDATE()
END
IF @ParentPostID = 0 -- New Post
BEGIN
-- Do INSERT into Posts table
INSERT dbo.Forum_Posts
(
ParentPostID,
UserID,
RemoteAddr,
Notify,
Subject,
Body,
CreatedDate,
PostLevel,
TreeSortOrder,
FlatSortOrder,
UpdatedDate,
IsApproved,
IsClosed,
FileAttachmentURL,
DateApproved
)
VALUES (
@ParentPostID,
@UserID,
@RemoteAddr,
@Notify,
@Subject,
@Body,
@@CreatedDate,
0,
0,
0,
@@CreatedDate,
@@ApprovedPost,
@IsClosed,
@FileAttachmentURL,
@@DateApproved
)
-- Create new thread
-- Get the new Post ID
SELECT @@PostID = @@IDENTITY
SET @@ThreadID = @@PostID
EXEC dbo.Forum_AA_ThreadAdd @@ThreadID, @ForumID, @IsPinned,
@PinnedDate, @ObjectID, @PollID
-- Update Posts with the new post id
UPDATE dbo.Forum_Posts
SET ThreadID = @@ThreadID
WHERE PostID = @@PostID
END
ELSE -- @ParentPostID <> 0 it's a reply to an existing post
BEGIN
-- Get post information for what we are replying to
SELECT @@ParentLevel = PostLevel,
@@ThreadID = ThreadID,
@@ParentTreeSortOrder = TreeSortOrder
FROM dbo.Forum_Posts
WHERE PostID = @ParentPostID
-- Calculate maximum flat sort order
SELECT @@MaxFlatSortOrder = MAX(FlatSortOrder)
FROM dbo.Forum_Posts
WHERE ThreadID = @@ThreadID
-- Is there another post at the same level or higher
IF EXISTS ( SELECT PostID
FROM dbo.Forum_Posts
WHERE PostLevel <= @@ParentLevel
AND TreeSortOrder > @@ParentTreeSortOrder
AND ThreadID = @@ThreadID )
BEGIN
-- Find the next post at the same level or higher
SELECT @@NextTreeSortOrder = MIN(TreeSortOrder)
FROM dbo.Forum_Posts
WHERE PostLevel <= @@ParentLevel
AND TreeSortOrder > @@ParentTreeSortOrder
AND ThreadID = @@ThreadID
-- Move the existing posts down
UPDATE dbo.Forum_Posts
SET TreeSortOrder = TreeSortOrder + 1
WHERE ThreadID = @@ThreadID
AND TreeSortOrder >= @@NextTreeSortOrder
-- And put this one into place
INSERT dbo.Forum_Posts
(
ParentPostID,
UserID,
RemoteAddr,
Notify,
Subject,
Body,
CreatedDate,
ThreadID,
PostLevel,
TreeSortOrder,
FlatSortOrder,
UpdatedDate,
IsApproved,
IsClosed,
FileAttachmentURL,
DateApproved
)
VALUES (
@ParentPostID,
@UserID,
@RemoteAddr,
@Notify,
@Subject,
@Body,
@@CreatedDate,
@@ThreadID,
@@ParentLevel + 1,
@@NextTreeSortOrder,
@@MaxFlatSortOrder + 1,
@@CreatedDate,
@@ApprovedPost,
@IsClosed,
@FileAttachmentURL,
@@DateApproved
)
-- Get the new post ID
SELECT @@PostID = @@IDENTITY
END
ELSE -- There are no posts at this level or above
BEGIN
-- Find the highest sort order for this parent
SELECT @@MaxTreeSortOrder = MAX(TreeSortOrder)
FROM dbo.Forum_Posts
WHERE ThreadID = @@ThreadID
-- Insert the new post
INSERT dbo.Forum_Posts
(
ParentPostID,
UserID,
RemoteAddr,
Notify,
Subject,
Body,
CreatedDate,
ThreadID,
PostLevel,
TreeSortOrder,
FlatSortOrder,
UpdatedDate,
IsApproved,
IsClosed,
FileAttachmentURL,
DateApproved
)
VALUES (
@ParentPostID,
@UserID,
@RemoteAddr,
@Notify,
@Subject,
@Body,
@@CreatedDate,
@@ThreadID,
@@ParentLevel + 1,
@@MaxTreeSortOrder + 1,
@@MaxFlatSortOrder + 1,
@@CreatedDate,
@@ApprovedPost,
@IsClosed,
@FileAttachmentURL,
@@DateApproved
)
-- Get the new post ID
SELECT @@PostID = @@IDENTITY
END
-- update thread
EXEC dbo.Forum_AA_ThreadUpdate @@ThreadID, @@PostID, @IsPinned,
@PinnedDate, 'postadd', @PollID
END
IF @@ApprovedPost = 1 -- it's not moderate forum
BEGIN
-- Update the user's post count
EXEC dbo.Forum_AA_UserPostCountUpdate @UserID, @PortalID
-- Update forum post count
EXEC dbo.Forum_AA_ForumPostCountUpdate @ForumID, @@ThreadID,
@@PostID, @UserID, 'add'
END
ELSE -- post need to be moderated
BEGIN
-- -- update forum moderated post count
EXEC dbo.Forum_AA_ForumPostCountUpdate @ForumID, @@ThreadID,
@@PostID, @UserID, 'submit'
END
SELECT @@PostID AS PostID,
@@GroupID AS GroupID
Use Script 3 to empty any test data you've got in the NTForums tables first:
--Written by AJM
--Clears threads and posts tables prior to import from ActiveForums
use bikeme
go
delete from forum_threads
go
delete from Forum_Posts
go
DBCC CHECKIDENT (Forum_Posts, RESEED,1)
go
delete from SearchItemWordPosition
go
DBCC CHECKIDENT (SearchItemWordPosition, RESEED,1)
go
delete from SearchItemWord
go
DBCC CHECKIDENT (SearchItemWord, RESEED,1)
go
delete from SearchWord
go
DBCC CHECKIDENT (SearchWord, RESEED,1)
go
delete from SearchItem
go
DBCC CHECKIDENT (SearchItem, RESEED,1)
Script 4 might come in handy, is self explanatory:
--Written by AJM
--Resets identity seed to 1 in Forum_Posts table
use bikeme_web
go
DBCC CHECKIDENT (Forum_Posts, RESEED,1)
Script 5 resets the stats before you do a live import
--Written by AJM
--Resets forum statistics prior to import of forums
USE bikeme
GO
UPDATE Forum_Forums
SET Forum_Forums.TotalPosts = 0,
Forum_Forums.EnablePostStatistics = 0,
Forum_Forums.MostRecentThreadID = 0,
Forum_Forums.MostRecentPostID = 0,
Forum_Forums.MostRecentPostDate = 0,
Forum_Forums.PostsToModerate = 0,
Forum_Forums.TotalThreads = 0,
Forum_Forums.MostRecentPostAuthorID = 0
GO
UPDATE Forum_Users
SET Forum_Users.PostCount=0
I'm not interested in supporting these - I did my migrate in December, it's no longer fresh in my mind. But you might find them handy in writing your own.
From memory, I had to add one working field to one table, you'll doubtless find it as you go through the scripts.