Help with the SQL Convert Script

Dec 27, 2012 at 11:44 AM
Edited Dec 27, 2012 at 11:45 AM

I'm trying for 2 days to get the script running. No matter what i do, i get errors and the script doesn't run well or at all (not sure if it does run partially).

I have tried within DNN SQL and with the SQL 2012 Management Studio, same errors.

My problem apparently is the search and replace as described here

If you are running this script in a SQL Query Window in SQL Server Management Studio (RECOMMENDED) search and replace the tokens {databaseOwner} and {objectQualifier} in the migration script. The values you will replace these tokens with will most likely be "dbo." and "", but you can verify by checking your web.config file and looking for objectQualifier and databaseOwner in there.

I search and replace all instances of {databaseOwner} with dbo. and i have no objectqualifier so i remove all instances of it.

Not sure what i do wrong.

An Example of my script:

 

 


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo.][dnntoaf_groups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo.][dnntoaf_groups]
GO

CREATE PROCEDURE [dbo.][activeforums_DefaultGroupSettings]
@ModuleId int,
@GroupId int
AS
DECLARE @GroupKey nvarchar(150)
SET @GroupKey = 'G:' + CAST(@GroupId as nvarchar(100))

INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWATTACH','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWEMOTICONS','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWHTML','false')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWPOSTICON','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWRSS','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWSCRIPT','false')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHCOUNT','3')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXHEIGHT','400')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXSIZE','1000')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXWIDTH','400')

Dec 27, 2012 at 2:45 PM
For some reason you are ending up with the period in the wrong place. It should be [dbo].[activeforums_Settings] not [dbo.][activeforums_Settings]

Thanks,
Will

On Dec 27, 2012, at 3:44 AM, "zaratustra" <notifications@codeplex.com> wrote:

From: zaratustra

I'm trying for 2 days to get the script running. No matter what i do, i get errors and the script doesn't run well or at all (not sure if it does run partially).

I have tried within DNN SQL and with the SQL 2012 Management Studio, same errors.

My problem apparently is the search and replace as described here

If you are running this script in a SQL Query Window in SQL Server Management Studio (RECOMMENDED) search and replace the tokens {databaseOwner} and {objectQualifier} in the migration script. The values you will replace these tokens with will most likely be "dbo." and "", but you can verify by checking your web.config file and looking for objectQualifier and databaseOwner in there.

I search and replace all instances of {databaseOwner} with dbo. and i have no objectqualifier so i remove all instances of it.

Not sure what i do wrong.

An Example of my script:

CREATE PROCEDURE [dbo.][activeforums_DefaultGroupSettings]
@ModuleId int,
@GroupId int
AS
DECLARE @GroupKey nvarchar(150)
SET @GroupKey = 'G:' + CAST(@GroupId as nvarchar(100))

INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWATTACH','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWEMOTICONS','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWHTML','false')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWPOSTICON','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWRSS','true')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ALLOWSCRIPT','false')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHCOUNT','3')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXHEIGHT','400')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXSIZE','1000')
INSERT INTO [dbo.][activeforums_Settings] (ModuleId, GroupKey, SettingName, SettingValue) VALUES (@ModuleId, @GroupKey, 'ATTACHMAXWIDTH','400')

Dec 27, 2012 at 3:13 PM

Thanks Will,

No errors now. But after the script .... the new forums are nowhere.

Dec 27, 2012 at 3:47 PM

 

I think i have used tabid instead of module id.

I will let you know the results.

 

Dec 28, 2012 at 12:24 AM
Edited Dec 29, 2012 at 1:52 PM

 

I have converted finally a small forum of mine to AF 5.0.2.

There are some problems in the converted posts, kinda complicated to describe. The problems are GONE if you edit and save the post.

The problems are some stray characters (skiank has provided a script to fix that), some wrong URLs in the Last Post page,  i don't see the # of posts each user has.

I will spend some days with it and post back my findings.

Thanks to the people that helped me.

Dec 28, 2012 at 4:04 PM

Try run my addon script.

*****************************************************************************
-- CREATE RECORDS IN USERPROFILE WITH #POST COUNT
*****************************************************************************
truncate table [activeforums_UserProfiles]
  INSERT INTO dbo.activeforums_UserProfiles
           ([UserId]
           ,[ModuleId]
           ,[PortalId]
           ,[TopicCount]
           ,[ReplyCount]
           ,[ViewCount]
           ,[AnswerCount]
           ,[RewardPoints]
           ,[UserCaption]
           ,[DateCreated]
           ,[DateUpdated]
           ,[DateLastActivity]
           ,[DateLastPost]
           ,[Signature]
           ,[SignatureDisabled]
           ,[TrustLevel]
           ,[AdminWatch]
           ,[AttachDisabled]
           ,[Avatar]
           ,[AvatarType]
           ,[AvatarDisabled]
           ,[PrefDefaultSort]
           ,[PrefDefaultShowReplies]
           ,[PrefJumpLastPost]
           ,[PrefTopicSubscribe]
           ,[PrefSubscriptionType]
           ,[PrefUseAjax]
           ,[PrefEmailFormat]
           ,[PrefBlockAvatars]
           ,[PrefBlockSignatures]
           ,[PrefPageSize]
           ,[Yahoo]
           ,[MSN]
           ,[ICQ]
           ,[AOL]
           ,[Occupation]
           ,[Location]
           ,[Interests]
           ,[WebSite]
           ,[Badges]
           ,[Bio])
  
  select v_members.UserID,'-1',@PortalID,isnull(TotalParentPosts,0),isnull(TotalReplyPosts,0),'0','0','0',NULL,Users.CreatedOnDate,null,getdate(),null,null
  ,'0','0','0','0',NULL,'0','0','ASC','0','0','0','0','1','0','0','0','20',null,null,null,null,null,null,null,null,null,'' from
  (
  select distinct UserID from dbo.Forum_Posts
  ) as v_members
  left join
  (
  select UserID,count(*) as TotalParentPosts from dbo.Forum_Posts
  where ParentPostID = 0 group by UserID
  ) as v_TotalParentPosts
 on v_members.UserID = v_TotalParentPosts.UserID
  left join
  (
  select UserID,count(*) as TotalReplyPosts from dbo.Forum_Posts
  where ParentPostID <> 0 group by UserID
  ) as v_TotalReplyPosts
 on v_members.UserID = v_TotalReplyPosts.UserID
  inner join dbo.Users
  on dbo.Users.UserID = v_members.UserID
where v_members.UserID not in
(select UserID from [activeforums_UserProfiles])
*****************************************************************************

Dec 29, 2012 at 12:49 AM

@skiank

Running your script fixed 2 problems of mine with stray characters and with Total Posts (user profiles).

Many many thanks.

The convert script should be updated because many people will stop there.