Henrik Fransas
May 30, 2018
  2594
(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
Zombie Properties want to Eat Your Brains

It’s a story as old as time. You work hard to build a great site. You have all the right properties – with descriptive names – that the content...

Joe Mayberry | Mar 29, 2023 | Syndicated blog

Optimizely finally releases new and improved list properties!

For years, the Generic PropertyList has been widely used, despite it being unsupported. Today a better option is released!

Tomas Hensrud Gulla | Mar 28, 2023 | Syndicated blog

Official List property support

Introduction Until now users were able to store list properties in three ways: Store simple types (int, string, DateTime, double) as native...

Bartosz Sekula | Mar 28, 2023

New dashboard implemented in CMS UI 12.18.0

As part of the CMS UI 12.18.0 release , a new dashboard has been added as a ‘one stop shop’ to enable editors to access all of their content items,...

Matthew Slim | Mar 28, 2023