November Happy Hour will be moved to Thursday December 5th.

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

Shared optimizely cart between non-optimizley front end site

E-commerce ecosystems often demand a seamless shopping experience where users can shop across multiple sites using a single cart. Sharing a cart...

PuneetGarg | Dec 3, 2024

CMS Core 12.22.0 delisted from Nuget feed

We have decided to delist version 12.22.0 of the CMS Core packages from our Nuget feed, following the discovery of a bug that affects rendering of...

Magnus Rahl | Dec 3, 2024

Force Login to Optimizely DXP Environments using an Authorization Filter

When working with sites deployed to the Optimizely DXP, you may want to restrict access to the site in a particular environment to only authenticat...

Chris Sharp | Dec 2, 2024 | Syndicated blog

Video Guides: Image Generation Features in Optimizely

The AI Assistant for Optimizely now integrates seamlessly with Recraft AI, providing advanced image generation capabilities directly within your...

Luc Gosso (MVP) | Dec 1, 2024 | Syndicated blog