SQL Script to fix problem with content tree not loading or set access rights in admin that is not working

Disclamber: You should propably never do like this unless it is completly nessesary since you are working directly agains the database of Epi and that can be dangerous so IF you do this, I will take no responsibilty in the script and you do it on your own risk and you should ALWAYS take a backup of the database first!

This is a post that is the result of problems with upgrading Lionbridge to the latest version that is descibed here: https://world.episerver.com/blogs/Henrik-Fransas/Dates/2018/5/sql-script-to-fix-problem-with-lionbridge-after-upgrade-to-the-version-for-episerver-11/.

This script solved it for one of our sites that had problem but for a couple of others it still didn't work and after a lot of trial and error we found out that we had content in the database that existed in tblContent but not in tblContentLanguage and that produced this error in the view for Setting access rights:

[KeyNotFoundException: The given key was not present in the dictionary.]

This error is since when that page is trying to build up the tree it first gets all the contents and then when looping through those it tries to get the language version of the content and that does not exist so this function in Episervers code (Episerver.dll) produce this error:

private void ReadCommonMetadata(DbDataReader reader, Dictionary<int, Dictionary<int, IContent>> allFetchedItems)
        {
            while (reader.Read())
            {
                foreach (KeyValuePair<int, IContent> item in allFetchedItems[Convert.ToInt32(reader["PageLinkID"])])
                {
                    IContent value = item.Value;
                    base.LoadIdentity(value, reader, false);
                    base.AssignMasterLanguage(value as ILocalizable, reader);
                    base.AssignResourceable(value as IResourceable, reader);
                    base.AssignContentResource(value as IContentAsset, reader);
                    base.AssignDeleteInfo(value, reader);
                    PageData pageDatum = value as PageData;
                    if (pageDatum != null)
                    {
                        base.LoadPageMetaData(pageDatum.Property, reader);
                    }
                    IContentSecurable contentSecurable = value as IContentSecurable;
                    if (contentSecurable == null)
                    {
                        continue;
                    }
                    this.LoadACLReferences(contentSecurable.GetContentSecurityDescriptor(), reader);
                }
            }
        }

Where this Convert.ToInt32(reader["PageLinkID"]) is returning 0 and that key does not exists so the page crash.

After reading this post that was not exact the same problem but simular error (https://world.episerver.com/Modules/Forum/Pages/Thread.aspx?id=149428) we looked for content that was not saved or deleted correctly and to find those content we run this SQL Script:

Select c.*
From tblContent c
	left outer join tblContentLanguage cl on c.pkID = cl.fkContentID
Where cl.fkContentID is null

And that showed that we had content that did not have a row in the table tblContentLanguage and that is making Episerver GUI to not work since it is expecting all content to have a row there and also in the table tblWorkContent.

To fix this, we add rows to tblContentLanguage and tblWorkContent by using this SQL script

DECLARE @contentId int
DECLARE @workContentId int
Declare @VisibleInMenu int
Declare @Name varchar(100)
Declare @LanguageBranchID int

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
select c.pkID, c.VisibleInMenu, c.fkMasterLanguageBranchID
From tblContent c
	left outer join tblContentLanguage cl on c.pkID = cl.fkContentID
Where cl.fkContentID is null

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @contentId, @VisibleInMenu, @LanguageBranchID
WHILE @@FETCH_STATUS = 0
BEGIN 
	
	set @Name = 'AutocreateForContent' + CAST(@contentId as varchar(10))

    Insert Into [dbo].[tblWorkContent]
	([fkContentID], [fkMasterVersionID], [ContentLinkGUID], [fkFrameID], [ArchiveContentGUID], [ChangedByName], [NewStatusByName], [Name], [URLSegment], [LinkURL], [BlobUri], [ThumbnailUri], [ExternalURL], [VisibleInMenu], [LinkType], [Created], [Saved], [StartPublish], [StopPublish], [ChildOrderRule], [PeerOrder], [ChangedOnPublish], [RejectComment], [fkLanguageBranchID], [CommonDraft], [Status], [DelayPublishUntil])
	Values
	(@contentId, NUll, NUll, NULL, NULL, '', NULL, @Name, @Name, NULL, NULL, NULL, NULL, @VisibleInMenu, 0, GETUTCDATE(), GETUTCDATE(), GETUTCDATE(), NULL, 1, 100, 0, NULL, @LanguageBranchID, 0, 4, NULL )

	SELECT @workContentId = CAST(SCOPE_IDENTITY() as int)

	Insert Into tblContentLanguage
	([fkContentID], [fkLanguageBranchID], [ContentLinkGUID], [fkFrameID], [CreatorName], [ChangedByName], [ContentGUID], [Name], [URLSegment], [LinkURL], [BlobUri], [ThumbnailUri], [ExternalURL], [AutomaticLink], [FetchData], [Created], [Changed], [Saved], [StartPublish], [StopPublish], [Version], [Status], [DelayPublishUntil])
	Select c.pkID, @LanguageBranchID, null, null, '', '', c.ContentGUID, @Name, null, null, null, null, null, 1, 0, GETUTCDATE(), GETUTCDATE(), GETUTCDATE(), GETUTCDATE(), null, @workContentId, 4, null
	From tblContent c
		left outer join tblContentLanguage cl on c.pkID = cl.fkContentID
	Where cl.fkContentID is null

    FETCH NEXT FROM MY_CURSOR INTO @contentId, @VisibleInMenu, @LanguageBranchID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR