Henrik Fransas
May 30, 2018
  3229
(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: 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

May 30, 2018

Comments

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
Custom form element view in Optimizely CMS 12

Do you want full control over the form element markup? Create your own views!

Tomas Hensrud Gulla | Dec 11, 2024 | Syndicated blog

How to Elevate Your Experimentation - Opticon workshop experience

As a non-expert in the field of experimentation, I’d like to share my feedback on the recent Opticon San Antonio workshop session titled "How to...

David Ortiz | Dec 11, 2024

Persisting a Strawberry Shake GraphQL Client for Optimizely's Content Graph

A recent CMS project used Strawberry Shake to generate an up-to-date C# GraphQL client at each build. But what happens to the build if the GraphQL...

Nicholas Sideras | Dec 11, 2024 | Syndicated blog

Opti ID with Secure Cookies And Third Party AddOns

Opti ID has revolutionised access to the Optimizely One suite and is now the preferred authentication method on all PAAS CMS websites that I build....

Mark Stott | Dec 9, 2024

AsyncHelper can be considered harmful

.NET developers have been in the transition to move from synchronous APIs to asynchronous API. That was boosted a lot by await/async keyword of C#...

Quan Mai | Dec 4, 2024 | Syndicated blog

The search for dictionary key

Recently I helped to chase down a ghost (and you might be surprised to know that I, for most part, spend hours to be a ghostbuster, it could be fun...

Quan Mai | Dec 4, 2024 | Syndicated blog