Henrik Fransas
May 28, 2018
  3338
(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
IDX21323 - RequireNonce is true, Nonce was null

We have multiple clients configured with Azure Active Directory (Microsoft Entra) for requiring authentication when accessing their website. The...

David Drouin-Prince | Oct 1, 2023 | Syndicated blog

Minimum Detectable Effect in Optimizely Web Experimentation

Understanding Minimum Detectable Effect Minimum Detectable Effect (MDE) is a core statistical calculation in Optimizely Web Experimentation. In...

Matthew Dunn | Oct 1, 2023 | Syndicated blog

Configured Commerce - Introduction to Long-Term Support (LTS) Releases

First off, for those who have not had a chance to meet me yet, my name is John McCarroll, and I am the Technical Product Manager for the Optimizely...

John McCarroll | Sep 29, 2023

Auto-translate with OpenAI in Optimizely CMS

You can now auto-translate content using your favorite online AI service, inside the old trustworthy Episerver.Labs.LanguageManager!

Tomas Hensrud Gulla | Sep 29, 2023 | Syndicated blog