Remove a Pre-Installed Language

How to Remove a Webshop Language

Step 1: Locate the variable '@LanguageId' in the script below and specify the locale ID of the language which should be removed. The language IDs can be found in the 'Languages' table of the Sana Commerce SQL database. This script can be also downloaded as a SQL file.

Step 2: Execute the SQL script on the Sana Commerce database to remove the specified webshop language:

CREATE FUNCTION remfld19823
(
 @xml XML,
 @field nvarchar(50)
)
RETURNS nvarchar(MAX)
AS
BEGIN
 DECLARE @value nvarchar(MAX);
 SELECT @value = N'<FieldsDictionary>'+CONVERT(nvarchar(max),@xml.query('
        for $v in /FieldsDictionary/*[@name != sql:variable("@field")]
        return $v'))+N'</FieldsDictionary>';
 RETURN @value;
END
GO
CREATE PROCEDURE remove_localized_field_21122011
(
 @fieldName nvarchar(255)
)
AS
BEGIN
 UPDATE Products SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE ProductCategories SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponentGroups SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponents SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
END
GO
BEGIN TRANSACTION
 BEGIN TRY
  DECLARE @languageId int;
  SET @languageId = [languageId]; /* the Locale ID of the language which should be removed */
  IF ((SELECT count(Id) FROM Languages WHERE Id = @languageId AND IsDefault = 1) > 0)
  BEGIN
   raiserror('ERROR: language %d is a system default language!', 11, 1, @languageId)
  END
  IF ((SELECT count(id) FROM Websites WHERE DefaultLanguageId = @languageId) > 0)
  BEGIN
   raiserror('ERROR: language %d is used as default for website!', 11, 1, @languageId)
  END
  --Delete NavigationItems
  DELETE FROM dbo.NavigationItems
  WHERE NavigationId IN (SELECT Id 
         FROM dbo.Navigations 
         WHERE LanguageId = @languageId);
  --Delete Navigations
  DELETE FROM dbo.Navigations
  WHERE LanguageId = @languageId;
  --Delete MobileNavigationItems
  DELETE FROM dbo.MobileNavigationItems
  WHERE NavigationId IN (SELECT Id 
         FROM dbo.MobileNavigations 
         WHERE LanguageId = @languageId);
    --Delete MobileNavigations
  DELETE FROM dbo.MobileNavigations
  WHERE LanguageId = @languageId;
    --Delete all translations 
  DELETE FROM ItemTranslations
  WHERE LanguageId = @languageId;
  --Delete WebsiteLanguages
  DELETE FROM WebsiteLanguages
  WHERE LanguageId = @languageId
  --Delete SanaTexts
  DELETE FROM SanaTexts
  WHERE LanguageId = @languageId
  --Delete MailTemplates
  DELETE FROM MailTemplates
  WHERE LanguageId = @languageId
  -- remove localized fields
  DECLARE @stringLanguageId nvarchar(255)
  SET @stringLanguageId = CAST (@languageId as nvarchar(255));
  DECLARE @fieldName nvarchar(255)
  SET @fieldName = 'Description_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Title_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Name_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'HorizontalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VerticalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VariantDescription_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  ------------
  --Delete LanguagePreference from ShopAccount
  UPDATE dbo.ShopAccounts SET Fields = dbo.remfld19823(Fields,'LanguagePreference')
  WHERE CAST( Fields
    .query('
     for $descr in /FieldsDictionary/*[@name = "LanguagePreference"]
     return string($descr)
    ')
    .value('/', 'nvarchar(max)') AS xml)
    .value('/', 'nvarchar(max)') = @languageId
  --Delete Languages
  DELETE FROM Languages 
  WHERE Id = @languageId;
 END TRY
 BEGIN CATCH
  PRINT ERROR_MESSAGE() + ' Transaction rolled back.'
  IF @@TRANCOUNT > 0
   ROLLBACK TRANSACTION
 END CATCH
GO
IF @@TRANCOUNT > 0
 COMMIT TRANSACTION
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remfld19823' 
    AND ROUTINE_TYPE = 'FUNCTION'
) 
    DROP FUNCTION remfld19823
GO
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remove_localized_field_21122011' 
    AND ROUTINE_TYPE = 'PROCEDURE'
) 
    DROP PROCEDURE remove_localized_field_21122011
GO