Try our conversational search powered by Generative AI!

Henrik Fransas
May 28, 2018
  3583
(5 votes)

SQL Script to fix problem with Lionbridge after upgrade to the version for Episerver 11

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!

We just upgraded a site that use the Lionbridge connector for translation (https://nuget.episerver.com/package/?id=LionbridgeConnector) and after that we had problems in for example admin when setting access rights.

The problem was that Episerver tried to load a dll that did not exist. After some inverstigation we found out that the connector creates three different content types and when we inspected the code we saw that they didn't defined the GUID for the content type. 

Because they did not do that and they had changed the namespace for where these content types were defined Episerver had created three new content types in the database and the old once is still there pointing to the old version of the dll.

This meens that if the site has done any translation projects all these will point to the old versions of the content types and all new translations will point to the new versions of the content types. This meens that when going into admin and set access right it tries to load content that points to the content with the content types that points to the removed dll and that part of the admin crash.

We have notified Lionbridge about it and they are working on a new version where they define the GUID and move all content but if you like to fix your site yourself you can run this sql script on the database (and restart the site after) and then you will see all the old translation projects and also be able to set access rights again.

This script only works between the version for 10 to 11 since it defines the whole path for the content type and also directs a problem where they renamed a property.
It is still problem between the version for 9 to 10 och 9 to 11 but you need to modify this script to get that to work.

And also, after running this, you need to go into admin and remove the old not working content types (those marked with a !)

IMPORTANT: Just noticed that they changed the version number for the latest version for 11 (we got a version before it was approved) so run this script to make sure you have correct path:

Select pkID, ContentTypeGUID, Created, ModelType, [Name]
From [dbo].[tblContentType]
Where ModelType like '%lionbridge%'



Declare @TranslationProjectOld int = 0
Declare @TranslationProjectNew int = 0

Declare @TranslationProjectAssetOld int = 0
Declare @TranslationProjectAssetNew int = 0

Declare @WorkspaceOld int = 0
Declare @WorkspaceNew int = 0

Declare @ItemRef int = 0

Select @TranslationProjectOld = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.TranslationProject, LionbridgeConnector.EPiServer100, Version=1.3.12.1000, Culture=neutral, PublicKeyToken=null'

Select @TranslationProjectNew = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.TranslationProject, LionbridgeConnector.EPiServer, Version=1.4.2.1100, Culture=neutral, PublicKeyToken=null'

Select @TranslationProjectAssetOld = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.TranslationProjectAsset, LionbridgeConnector.EPiServer100, Version=1.3.12.1000, Culture=neutral, PublicKeyToken=null'

Select @TranslationProjectAssetNew = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.TranslationProjectAsset, LionbridgeConnector.EPiServer, Version=1.4.2.1100, Culture=neutral, PublicKeyToken=null'

Select @WorkspaceOld = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.Workspace, LionbridgeConnector.EPiServer100, Version=1.3.12.1000, Culture=neutral, PublicKeyToken=null'

Select @WorkspaceNew = pkID
From [dbo].[tblContentType]
Where ModelType = 'Lionbridge.Translate.Workspace, LionbridgeConnector.EPiServer, Version=1.4.2.1100, Culture=neutral, PublicKeyToken=null'

Select @ItemRef = pkID
From [tblPropertyDefinition]
Where fkContentTypeID IN (@TranslationProjectNew, @TranslationProjectAssetNew, @WorkspaceNew) and [Name] = 'ItemRef'

if(@TranslationProjectOld <> 0 and @TranslationProjectNew <> 0)
Begin
Update cp
	set fkPropertyDefinitionID = (Select newPD.pkID
									From [dbo].[tblPropertyDefinition] pd
										inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @TranslationProjectNew) newPD on pd.[Name] = newPD.[Name]
									Where pd.pkID = cp.fkPropertyDefinitionID)
	From tblContentProperty cp  
		inner Join tblContent c on c.pkID = cp.fkContentID
	Where c.fkContentTypeID = @TranslationProjectOld
End

if(@TranslationProjectAssetOld <> 0 and @TranslationProjectAssetNew <> 0)
Begin
Update cp
	set fkPropertyDefinitionID = IsNull((Select newPD.pkID
											From [dbo].[tblPropertyDefinition] pd
												inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @TranslationProjectAssetNew) newPD on pd.[Name] = newPD.[Name]
											Where pd.pkID = cp.fkPropertyDefinitionID), @ItemRef)
	From tblContentProperty cp  
		inner Join tblContent c on c.pkID = cp.fkContentID
	Where c.fkContentTypeID = @TranslationProjectAssetOld
End

if(@WorkspaceOld <> 0 and @TranslationProjectNew <> 0)
Begin
	Update cp
	set fkPropertyDefinitionID = (Select newPD.pkID
									From [dbo].[tblPropertyDefinition] pd
										inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @WorkspaceNew) newPD on pd.[Name] = newPD.[Name]
									Where pd.pkID = cp.fkPropertyDefinitionID)
	From tblContentProperty cp  
		inner Join tblContent c on c.pkID = cp.fkContentID
	Where c.fkContentTypeID = @WorkspaceOld
End

if(@TranslationProjectOld <> 0 and @WorkspaceNew <> 0)
Begin
Update cp
	set fkPropertyDefinitionID =  (Select newPD.pkID
									From [dbo].[tblPropertyDefinition] pd
										inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @TranslationProjectNew) newPD on pd.[Name] = newPD.[Name]
									Where pd.pkID = cp.fkPropertyDefinitionID)
	From [tblWorkContentProperty] cp  
		inner Join tblWorkContent wc on cp.fkWorkContentID = wc.pkID
		inner Join tblContent c on c.pkID = wc.fkContentID
	Where c.fkContentTypeID = @TranslationProjectOld
End

if(@TranslationProjectAssetOld <> 0 and @TranslationProjectAssetNew <> 0)
Begin
Update cp
	set fkPropertyDefinitionID = ISNULL((Select newPD.pkID
											From [dbo].[tblPropertyDefinition] pd
												inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @TranslationProjectAssetNew) newPD on pd.[Name] = newPD.[Name]
											Where pd.pkID = cp.fkPropertyDefinitionID), @ItemRef)
	From [tblWorkContentProperty] cp  
		inner Join tblWorkContent wc on cp.fkWorkContentID = wc.pkID
		inner Join tblContent c on c.pkID = wc.fkContentID
	Where c.fkContentTypeID = @TranslationProjectAssetOld
End

if(@WorkspaceOld <> 0 and @WorkspaceNew <> 0)
Begin
	Update cp
	set fkPropertyDefinitionID =  (Select newPD.pkID
									From [dbo].[tblPropertyDefinition] pd
										inner join (Select pkID, [Name] From [tblPropertyDefinition] Where fkContentTypeID = @WorkspaceNew) newPD on pd.[Name] = newPD.[Name]
									Where pd.pkID = cp.fkPropertyDefinitionID)
	From [tblWorkContentProperty] cp  
		inner Join tblWorkContent wc on cp.fkWorkContentID = wc.pkID
		inner Join tblContent c on c.pkID = wc.fkContentID
	Where c.fkContentTypeID = @WorkspaceOld
End

if(@TranslationProjectOld <> 0 and @TranslationProjectNew <> 0)
Begin
	Update tblContent
	Set fkContentTypeID = @TranslationProjectNew
	Where fkContentTypeID = @TranslationProjectOld
End

if(@TranslationProjectAssetOld <> 0 and @TranslationProjectAssetNew <> 0)
Begin
	Update tblContent
	Set fkContentTypeID = @TranslationProjectAssetNew
	Where fkContentTypeID = @TranslationProjectAssetOld
End

if(@WorkspaceOld <> 0 and @WorkspaceNew <> 0)
Begin
	Update tblContent
	Set fkContentTypeID = @WorkspaceNew
	Where fkContentTypeID = @WorkspaceOld
End


May 28, 2018

Comments

Quan Pham
Quan Pham May 29, 2018 03:40 AM

Thx for your very quick solution :)!

Please login to comment.
Latest blogs
Change the IP HTTP Header used for geo-lookup in Application Insights

.

Johan Kronberg | Jun 10, 2024 | Syndicated blog

Copying property values

In this article I’d like to show simple Edit Mode extension for copying property values to other language versions. In one of my previous blogposts...

Grzegorz Wiecheć | Jun 8, 2024 | Syndicated blog

Auto-translate with OpenAI GPT-4o in Optimizely CMS

Improvements for Episerver.Labs.LanguageManager! It's now possible to auto-translate both a page and its children at the same time! Additionally, m...

Tomas Hensrud Gulla | Jun 7, 2024 | Syndicated blog

Upgrade To Optimizely CMS 12 Issue: List item fields have become Required

There are many funny details to be aware of when upgrading from Episerver CMS 11 to Optimizely CMS 12. One of them that might feel a bit confusing ...

Allan Thraen | Jun 7, 2024 | Syndicated blog

Integrating Apple Pay within Optimizely - Lesson's Learned

Hello Optimizely world, I have recently undertaken the responsibility of assisting our client in the integration of Apple Pay onto their website...

Silvio Pacitto | Jun 6, 2024

A day in the life of an Optimizely Developer - Introduction to Optimizely SaaS Core

What is SaaS Core Optimizely SaaS Core is a cloud-based, headless, and API-first version of the Optimizely CMS (Content Management System) and...

Graham Carr | Jun 4, 2024