I was able to get the process to run but had to hack at the DB so bad that it was unusable afterwards.  ASI has identified it as a High Level bug and is  work on a resolution.  if you want more details I can dig up the script i used to do the cleanup.

Troy Cantrell Director Information Technology California Travel and Tourism Commission

Hi Troy,

Are you referring to SMR190079?  I recently researched this SMR and found that it was not a bug with the migration tool but an issue with the User business object.  For some reason some 15.1 databases have the wrong User business object published.  The User BO should only reference the UserMain table but the version published on several databases has included the Users table as well.

Nelson - check to make sure that the published User BO in your database was created on 13 February 2008.

Cheers,
Courtney

P.S.  There is one bug with the migration tool that I'm aware of - SMR193383.

F. Nelson Stover, Intelligent Technologies, Inc.

This is what I am seeing in SQL.  I get an error in iSA when I try to access the Business Objects.

ALTER

VIEW [dbo].[vBoUser] AS SELECT UserMain.ContactMaster,UserMain.EffectiveDate,UserMain.ExpirationDate,UserMain.IsDisabled,UserMain.UpdatedByUserKey,UserMain.UpdatedOn,UserMain.UserId,UserMain.CreatedByUserKey,UserMain.CreatedOn,UserMain.UserKey,UserMain.DefaultDepartmentGroupKey,UserMain.DefaultPerspectiveKey,UserMain.ProviderKey,Users.AccessKeywords,Users.CheckTickler AS CheckTasksOnLogin,Users.EditTypes AS SpecificTypes,Users.IsCasualUser,Users.LevelCashAR,Users.LevelCert,Users.LevelDues,Users.LevelExpo,Users.LevelFundRaising,Users.LevelMeeting,Users.LevelMembership,Users.LevelOrderEntry,Users.LevelReferral,Users.LevelSC,Users.LevelSystem,Users.Signature,Users.StandardClosing1 AS LetterClosing,Users.StandardClosing2 AS TitleOther,Users.UserId AS CsUserId,Users.UserName AS CsUserName,Users.UPDATED_BY AS UpdatedBy FROM

UserMain

INNER JOIN

Users

ON UserMain.UserId = Users.

-- Should I have some other Business Object definition?

It looks like you have the same problem a lot of people do with the User BO.  When do you get an error in iSA?  When you try to open the BO or when you click on it?  If you can click on it, click the Versions button on the toolbar and revert to the version created Feb. 13.  Then try to publish the reverted version.  That should fix your problem.

Hope this helps,
Courtney

F. Nelson Stover, Intelligent Technologies, Inc.

When I am in iSA in the Full View and click on the link to Business Object Designer (http://sql1/imis15/Admin/iSA/Business%20object%20designer/AsiCommon/Controls/BOA/Default.aspx?TemplateType=A) I get an error saying that "an entry with the same key already exists".  I can't get to the business objects.

Thanks.

Nelson

Did you try clicking on the navigation for Business object designer as well as the link on the big button panel?  Have you tried accessing it from the Desktop?

F. Nelson Stover, Intelligent Technologies, Inc.

The only way I could get to the business objects was through Document System.  I navigated to Common >> Business Objects and found User.  There is only one version dated 9/9/03. 

How do I get the most recent one?

Nelson

The BO you see in the Document System is actually just a reference to the BO for IQA purposes.  (Sort of like a shortcut.)  The actual BO can only be reached via the document system.  I'm not sure why you're getting an error when trying to reach BOD.  Perhaps check to see if the navigation for that has been altered in the web view sitemap (navigations are versioned just like BOs).

There is an alternative to getting this working for now, but make sure you backup your database before trying it :)

Run the following query:

DocumentKey, DocumentVersionKey, DocumentName, CreatedOn, DocumentStatusCode from DocumentMain where DocumentName = 'User' and DocumentTypeCode = 'BOD'

Select

You should see a few version of this document, and only one should have a status of 40 (published).  Update that version to have a status of 50 and the version created on Feb 13 to have a status of 40.  Then run BuildPublishBOs.exe located in Net/bin under your install folder (make sure the config file is pointing to the right database first).  This could take a while to run depending on how many BOs you have and what the connection speed is to the database but it should solve your problem.

F. Nelson Stover, Intelligent Technologies, Inc.

Thanks -- I'll try this tomorrow.

Nelson

Do you know which security group it was copying when it threw the error?  My guess is that it added the role for the security group but since it didn't finish adding the users to the role it didn't log it as being done.  Maybe you can try deleting the security group role from RoleMain.  Otherwise a db restore is going to be your only option.

F. Nelson Stover, Intelligent Technologies, Inc.

Do I have to restore the entire database and start over?  Do you have a script that resets what has been done?  What's the best way to proceed?

Thanks.

Nelson

Given the amount of changes to the DB I felt it was best to restore.  If you are live there are probably tables that could be cleared out.

Troy Cantrell Director Information Technology California Travel and Tourism Commission

It looks like the User business object thinks the UserId field is only 50 characters instead of 60.  I think you'll have to figure out what is going on with you Business object designer and update the User BO to have a 60 character UserId field.

I wish I had better news for you :-/  I'll create an SMR for the error on the User BO.

F. Nelson Stover, Intelligent Technologies, Inc.

Thanks Courtney -- I republished the USER BO and got through the security groups.  Below are the SQL snippets that I used to reset the CM5Migration file so that I could restart the process.

Delete from UserRole
where RoleKey in (Select RoleKey from RoleMain
Where IsSystem = 0
and Name <> 'Basic')

Delete RoleMain
Where IsSystem = 0
and Name <> 'Basic'

Delete CM5Migration
Where ImportItemType = 'Login_Security_Group' 

Delete CM5Migration
Where ImportItemType = 'Security_Groups'

Delete CM5Migration
Where ImportItemType = 'ErrorMessage'

F. Nelson Stover, Intelligent Technologies, Inc.

The migration has gotten through the security groups and websites -- I can see these in WCM.

In the section Copying Content Folders .. the error is

Error during migration: System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'

at CMMigrationTool.ContentManagerUpgrade.CopyContentFolders()

Check the Nav_Menu table.  Are there any null entries in NavContentGroupInd, Title or DirectoryName?  It smells like an SMR to me but you should be able to update nulls to blank in those 3 fields in order to keep moving with your migration.

F. Nelson Stover, Intelligent Technologies, Inc.

I checked the Nav_Menu table and found 17 records with null values in DirectoryName -- I set these to blank.

Then the error shifted to Copying Content Folders.. Error during migration:  ASI.Business.CommitWithErrorsException: Document Constraint Violations:  PropertyName=Name;Message=Required Field; Severity=Error at ASI.Bsiness.BusinessItem.CheckObjectConstraintsForCommit()

Your assistance is appreciated.

Nelson

Scratch what I said about setting DirectoryName to blank.  This becomes the new folder name and cannot be blank.  The error message is complaining that ContentFolder.Name (DirectoryName) it is a required field.

F. Nelson Stover, Intelligent Technologies, Inc.

I made up names for the blank DirectoryName fields -- I didn't actually check that they were unique but I suspect they were.  Then I reran the migration.

The process copied 1109 Folders and then over 11300 records.  At the end it said:  Error during migration:  Asi.Bussiness.CommonDocumentStatusChangeException:  There is already a version of this document in the process of being published.  at CM5MigrationsTool.ContentManagerUpgrade.CreateContent(OleDbDataReader reader, Decimal contentId)

Is it really done?  Should I try again?

Thanks for all your help.

Nelson

Glad that got you through that hurdle :)

But I'm guessing you have more content records to go (plus a few other goodies).  Looks like you ran into SMR 193383.  If I were you I'd enter another SMR so the one I entered gets a higher priority.  The reason you're getting the error is because the Content it's trying to convert is probably in a published state, but a related piece of content (same OriginalContentID) with a smaller ContentID is already in a working or pending state.  To work around this for now, find out which piece of content it's coughing on (should be the next ContentID from the last one in the log table) and set the status to archived or recycled.  Then, after you're finished migrating all of your data fix that piece of content to have the right version published and the right one working.

F. Nelson Stover, Intelligent Technologies, Inc.

After fixing all the bad records with the script below, the process went through to the end, including the Navigation Items

I used the following to fix the Content table so that the all imported properly.

Update

CASet CA.WorkFlowStatusCode = 'R'

from Content CP, Content CAwhere CP.WorkFlowStatusCode = 'P'

and CA.WorkFlowStatusCode in ('Y','A')

and CP.OriginalContentID = CA.OriginalContentID

eCM to wCM still crashing on content records now.  Checked for any null content ID's and other issues noted above but still getting this error.

Copying content records...100...200...300...400...500...600...700...800...900...
1000...1100...1200...1300...1400...1500...1600...1700...1800...1900...2000...210
0...2200...2300...2400...2500...2600...2700...2800...2900...3000...3100...3200..
.3300...3400...3500...3600...3700...3800...3900...4000...4100...4200...4300...44
00...4500...4600...4700...4800...4900...5000...5100...5200...

Error during migration: System.InvalidCastException: Unable to cast object of type 'System.DBNull'
to type 'System.String'.
   at CM5MigrationTool.ContentManagerUpgrade.CreateContent(OleDbDataReader
reader, Decimal contentId)
   at CM5MigrationTool.ContentManagerUpgrade.CopyContent()
   at CM5MigrationTool.ContentManagerUpgrade.Run()
Migration complete, total run time 18:35:02.2557550.

Troy Cantrell Director Information Technology California Travel and Tourism Commission

Check to see if any of these are null in the Content table:  Name, URLSafeName, ShowInTemplateFlag or WorkflowStatusCode.  Also, check the Login view and make sure there are no null entries for iMISMemberID.

And you looked for nulls in Nav_Menu.Title as mentioned in one of the above comments?  Check Content.OriginalContentID as well.  If neither of these have null values either then I'm at a loss; nothing else requires a non-null value and gets casted as a string in the CreateContent method.  This is the exact error you are getting, right? 

Error during migration: System.InvalidCastException: Unable to cast object of type 'System.DBNull'
to type 'System.String'.
   at CM5MigrationTool.ContentManagerUpgrade.CreateContent(OleDbDataReader
reader, Decimal contentId)
   at CM5MigrationTool.ContentManagerUpgrade.CopyContent()
   at CM5MigrationTool.ContentManagerUpgrade.Run()
Migration complete, total run time 18:35:02.2557550.

If the error occured elsewhere I'd recommend looking for nulls in Content_HTML, Content_Link, Content_File or Content_Security_Group tables.

Yes, checked nav-menu.title and that is clean, no NULLs.

I did find 89  records where OriginalContentID is null, What should I set them to?

Yes, exact error.  Although I have since deleted 70,000 name security records to get the run time down but still get the exact same error except for the run time.

Troy Cantrell Director Information Technology California Travel and Tourism Commission

You can set OriginalContentID equal to ContentID where it's null.

Good luck.  If this change doesn't help I'm out of ideas :-/

I think i have the solution, will update the post when the process finishes. 

short answer, a content record with a contactid with no name_security record.

More to come....

Troy Cantrell Director Information Technology California Travel and Tourism Commission

Resolved eCM to wCM migration.  

All done in a test environment on test DB moving eCM in one db to wCM in another db.  Prior to launching I deleted most of our name_security records because the process was taking over 18 hours to convert them.  We narrowed it down to only name_security records that had previously logged in.  This took it down from 102,000  records to about 3,000 and took about 17 hours out of the time.  This could be due to the fact that it is a VM test environment and we will test again on a full live database soon.

First error we ran into was eCM does not require a full user license to be able to manage content in eCM so we have eCM users that edit content that are not full iMIS users, this causes a failure in the migration.  We have increased our iMIS license count due to growth so we can fix this.  You may need to clean up old content records updated and or owned by former users.

This query shows you the content records that will crash the upgrade.  

Select contentID from content

left join name_security on content.contactid = name_security.contactid

left join users on users.userid = name_security.web_login

left join name on name_security.id = name.id

where users.userid is null

We updated the contactID on these content records to our master admin account.

This is the actual error:Unable to log in as the user with contact ID 1674623.

Error during migration: System.Exception: Unable to contine migration

due to previous error.     

at CM5MigrationTool.ContentManagerUpgrade.CreateContent (OleDbDataReader reader, Decimal contentId)

at CM5MigrationTool.ContentManagerUpgrade.CopyContent()

at CM5MigrationTool.ContentManagerUpgrade.Run()

Second Error:

We found that content that was flagged as ContentApproverFlag true or ContentEditorFlag true and the name_security record did not exist caused the error.

So this function CreateContent(OleDbDataReader reader, decimal contentId) must have some code that looks at the approver and editor flag and if one of them is true it gets the imisMemberID and must cast it as a string without checking for a dbnull.  With a missing name_security record the imisMemberid is null as causes the error.  

This is the query from a trace of the eCM to wCM that had the null imismemberid that we tracked down.  

SELECT Content.ContentID,Content.OriginalContentID,content.membersonlyflag, content.WorkflowStatusCode,content.ArchiveAtExpirationFlag,content.OwnerContactID,Content_Authority_Producer.ContentAuthorityGroupID,

Content.*,(SELECT Count(*) FROM Content_HTML WHERE Content.ContentID = Content_HTML.ContentID) AS HTMLCount,

                                  (SELECT Count(*) FROM Content_File WHERE Content.ContentID = Content_File.ContentID) AS FileCount,

                                   (SELECT Count(*) FROM Content_Link WHERE Content.ContentID = Content_Link.ContentID) AS LinkCount,

                                   Nav_Menu.ContentAuthorityGroupID,

                                   Nav_Menu.ComponentCode AS NavComponentCode,

                                   Nav_Menu.ComponentScriptCode AS NavComponentScriptCode,

                                   Content_Authority_Producer.ContentEditorFlag,

                                   Content_Authority_Producer.ContentApproverFlag,

                                   Login.iMISMemberID,

                                   Nav_Menu.NavMenuID AS ActualNavMenuID,

                                   Nav_Menu.Title

                             FROM Content

                       INNER JOIN Content c2 ON Content.OriginalContentID = c2.OriginalContentID AND c2.WorkflowStatusCode = (SELECT TOP 1 c3.WorkflowStatusCode FROM Content c3 INNER JOIN Workflow_Status_Ref ON c3.WorkflowStatusCode = Workflow_Status_Ref.WorkflowStatusCode AND Workflow_Status_Ref.WorkflowStatusCode <> 'R' WHERE OriginalContentID = c2.OriginalContentID ORDER BY Workflow_Status_Ref.SortOrder)

                       INNER JOIN Nav_Menu ON c2.NavMenuID = Nav_Menu.NavMenuID

                                          AND Nav_Menu.WebsiteKey != 'EB4006C8-EB7A-48AA-9861-6B2C959E5549'

                        LEFT JOIN Content_Authority_Producer ON Nav_Menu.ContentAuthorityGroupID = Content_Authority_Producer.ContentAuthorityGroupID

                                                            AND Content.ContactID = Content_Authority_Producer.ContactID

                                                            AND (Content_Authority_Producer.ContentApproverFlag = 'Y' OR Content_Authority_Producer.ContentEditorFlag = 'Y')

                                                           AND Content.WorkflowStatusCode = 'W'

                        LEFT JOIN Login ON Content_Authority_Producer.ContactID = Login.ContactID

            ORDER BY Content.ContentID

Took us a while to track it down because it only happens on records where the approver or editor flag is set to Y.

additionaly the fix for records with smaller working or published contentid,  add the WorkFlowStatusCode of 'E' (pending approval) as it will crash the upgrade as well

--Finds records with smaller working or published contentid
Select *
from Content CP, Content CA
where CP.WorkFlowStatusCode = 'P'
and CA.WorkFlowStatusCode in ('Y','A','E')
and CP.OriginalContentID = CA.OriginalContentID

--Fix records with smaller working or published contentid
Update CA Set CA.WorkFlowStatusCode = 'R'
from Content CP, Content CA
where CP.WorkFlowStatusCode = 'P'
and CA.WorkFlowStatusCode in ('Y','A','E')
and CP.OriginalContentID = CA.OriginalContentID

--Finds the bad records
Select *
from Content CP, Content CA
where CP.WorkFlowStatusCode = 'P'
and CA.WorkFlowStatusCode in ('Y','A','E')
and CP.OriginalContentID = CA.OriginalContentID

An option to add verbose logging to the upgrade would be nice to trouble shoot these issues.

Found one more issue.  We ran into the same missing login for the next step of the process which is to migrage nav. 

This did the trick.

-- Sets OwnerContactID to manager for nav_menu items with old employee data

Update nav_menu set Ownercontactid = 99999    -- Manager account contactID
where navmenuid in(select navmenuid from nav_menu
left join name_security on nav_menu.ownercontactid = name_security.contactid
left join users on users.userid = name_security.web_login
left join name on name_security.id = name.id
where users.userid is null )

Troy Cantrell Director Information Technology California Travel and Tourism Commission