Try our conversational search powered by Generative AI!

Henrik Fransas
May 30, 2018
(6 votes)

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:

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)
                    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 ( 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

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

FETCH NEXT FROM MY_CURSOR INTO @contentId, @VisibleInMenu, @LanguageBranchID
	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])
	(@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

May 30, 2018


Aria Zanganeh
Aria Zanganeh May 31, 2018 12:36 AM

a friend had this issue .. this helped :) tnx for sharing .. 

Henrik Fransas
Henrik Fransas May 31, 2018 07:05 AM

Always great to be able to help.

I feelt that this had to be a post since I could not find any info about it and the solution was not "out of the box" easy if you are not familiar with Episervers database.

See you Aria at the next EMVP Summit!

Aria Zanganeh
Aria Zanganeh Jun 2, 2018 03:41 PM

Thanks my friend..  I hope I hope..  Next time somewhere outside us! !

Henrik Fransas
Henrik Fransas Jun 4, 2018 06:54 AM

Aria, I do to, maybe Australia ;-)

Please login to comment.
Latest blogs
Microsoft announces Natural language to SQL

Finally, Microsoft launches "Natural language to SQL," after it has been available for several months in Optimizely CMS!

Tomas Hensrud Gulla | May 23, 2024 | Syndicated blog

Five easy ways to start personalizing your content right now

If you clicked on this article, you already know that getting the right message to the right person at the right time helps drive conversions and...

Kara Andersen | May 23, 2024

ExtendedCms.TinyMceEnhancements – serwer side webp support

Today I will introduce another small feature of TinyMceEnhancements plugin. The functionality is used to automatically detect whether a browser...

Grzegorz Wiecheć | May 22, 2024 | Syndicated blog

Azure AI Language– Detect Healthcare Content in Optimizely CMS

In this blog post, I showcase how the Azure AI Language service's Text Analytics for health feature can be used to detect healthcare content within...

Anil Patel | May 22, 2024 | Syndicated blog