Easy2Patch – Deployment Type Uninstall Command Line Fix

Overview

In some Easy2Patch environments, a database-related error may occur when creating or processing application records that do not contain an Uninstall Command Line value.

The issue is caused by the DeploymentTypeUninstallCommandLine field in the dbo.t_AppDts table of the Easy2Patch database not allowing NULL values.

This article explains how to apply the fix that updates the relevant database field to allow NULL values.

Affected Component

• Product: Easy2Patch
• Database: Easy2Patch
• Table: dbo.t_AppDts
• Field: DeploymentTypeUninstallCommandLine

Issue

Because the DeploymentTypeUninstallCommandLine field is mandatory, application records that do not have an uninstall command may not be saved to the database in some cases.

This may result in an SQL-related error while creating or processing the relevant records.

Resolution

The fix script below updates the DeploymentTypeUninstallCommandLine field so that it accepts NULL values.

The fix:

• Does not modify the contents of existing application records.
• Recreates the dbo.t_AppDts table using the updated schema.
• Transfers the existing records to the new table.
• Allows the DeploymentTypeUninstallCommandLine field to accept NULL values.

[!IMPORTANT] Because the dbo.t_AppDts table is recreated during this operation, application access to the table should be stopped. We recommend applying the fix during a planned maintenance window.

Before Applying the Fix

Complete the following steps before applying the fix:

1. Take a current and restorable backup of the Easy2Patch database.
2. Apply the fix during a planned maintenance window whenever possible.
3. Stop the Easy2Patch services.
4. Verify that you are connected to the correct SQL Server instance in SQL Server Management Studio.
5. Make sure the query will be executed against the Easy2Patch database.

Applying the Fix

1. Stop the Easy2Patch services.
2. Open SQL Server Management Studio.
3. Connect to the SQL Server instance hosting the Easy2Patch database.
4. Open a new query window.
5. Run the script below.
6. After the script completes successfully, follow the steps in the Validation section.
7. Restart the Easy2Patch services.

SQL Fix Script

USE Easy2Patch
GO

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppDts_CatalogType
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_DeploymentTypeDtIsCustomScript
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_DeploymentTypeScriptLanguage
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_DeploymentTypeExecuteTime
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_DeploymentTypePinOnClient
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_DeploymentTypeEnabled
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_ScriptResultComparisonValue
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_ScriptRunAs32Bit
GO

ALTER TABLE dbo.t_AppDts
    DROP CONSTRAINT DF_t_AppsDt_IsDeleted
GO

CREATE TABLE dbo.Tmp_t_AppDts
(
    Id uniqueidentifier NOT NULL,
    AppType int NOT NULL,
    DeploymentTypeTitle nvarchar(256) NOT NULL,
    DeploymentTypeTechnology nvarchar(256) NOT NULL,
    DeploymentTypeDescription nvarchar(256) NULL,
    DeploymentTypeLanguage nvarchar(5) NOT NULL,
    DeploymentTypeContentFolder nvarchar(256) NULL,
    DeploymentTypeUninstallContentFolder nvarchar(256) NULL,
    DeploymentTypeUninstallContentSetting int NOT NULL,
    DeploymentTypeInstallCommandLine nvarchar(256) NOT NULL,
    DeploymentTypeUninstallCommandLine nvarchar(256) NULL,
    DeploymentTypeRepairCommandLine nvarchar(256) NULL,
    DeploymentTypeDetectionScript nvarchar(MAX) NOT NULL,
    DeploymentTypeDtScriptJson nvarchar(MAX) NOT NULL,
    DeploymentTypeDtIsCustomScript int NOT NULL,
    DeploymentTypeScriptLanguage int NOT NULL,
    DeploymentTypeProcessInfoName nvarchar(256) NULL,
    DeploymentTypeRequiresUserInteraction int NOT NULL,
    DeploymentTypeRequiresLogOn int NULL,
    DeploymentTypeUserInteractionMode int NOT NULL,
    DeploymentTypePostExecutionBehavior int NOT NULL,
    DeploymentTypeExecutionContext int NOT NULL,
    DeploymentTypeMachineInstall int NOT NULL,
    DeploymentTypeMaxExecuteTime int NOT NULL,
    DeploymentTypeExecuteTime int NOT NULL,
    DeploymentTypeSlowContentHandlingMode int NOT NULL,
    DeploymentTypeFallbackToUnprotectedDP int NOT NULL,
    DeploymentTypePinOnClient int NOT NULL,
    DeploymentTypeEnabled int NOT NULL,
    ScriptResultComparisonValue nvarchar(50) NOT NULL,
    ScriptRunAs32Bit bit NOT NULL,
    CreatedAt datetime NULL,
    ModifiedAt datetime NULL,
    CreatedBy uniqueidentifier NULL,
    ModifiedBy uniqueidentifier NULL,
    IsDeleted int NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_t_AppDts
    SET (LOCK_ESCALATION = TABLE)
GO

DECLARE @v sql_variant
SET @v = N'1catalog, 2CustomCatalog,3CustomInjection'

EXECUTE sp_addextendedproperty
    N'MS_Description',
    @v,
    N'SCHEMA',
    N'dbo',
    N'TABLE',
    N'Tmp_t_AppDts',
    N'COLUMN',
    N'AppType'
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppDts_CatalogType DEFAULT ((1)) FOR AppType
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_DeploymentTypeDtIsCustomScript DEFAULT ((0))
    FOR DeploymentTypeDtIsCustomScript
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_DeploymentTypeScriptLanguage DEFAULT ((0))
    FOR DeploymentTypeScriptLanguage
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_DeploymentTypeExecuteTime DEFAULT ((5))
    FOR DeploymentTypeExecuteTime
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_DeploymentTypePinOnClient DEFAULT ((0))
    FOR DeploymentTypePinOnClient
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_DeploymentTypeEnabled DEFAULT ((1))
    FOR DeploymentTypeEnabled
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_ScriptResultComparisonValue DEFAULT (N'NeedUpdate')
    FOR ScriptResultComparisonValue
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_ScriptRunAs32Bit DEFAULT ((0))
    FOR ScriptRunAs32Bit
GO

ALTER TABLE dbo.Tmp_t_AppDts ADD CONSTRAINT
    DF_t_AppsDt_IsDeleted DEFAULT ((0))
    FOR IsDeleted
GO

IF EXISTS (SELECT * FROM dbo.t_AppDts)
    EXEC('
        INSERT INTO dbo.Tmp_t_AppDts
        (
            Id,
            AppType,
            DeploymentTypeTitle,
            DeploymentTypeTechnology,
            DeploymentTypeDescription,
            DeploymentTypeLanguage,
            DeploymentTypeContentFolder,
            DeploymentTypeUninstallContentFolder,
            DeploymentTypeUninstallContentSetting,
            DeploymentTypeInstallCommandLine,
            DeploymentTypeUninstallCommandLine,
            DeploymentTypeRepairCommandLine,
            DeploymentTypeDetectionScript,
            DeploymentTypeDtScriptJson,
            DeploymentTypeDtIsCustomScript,
            DeploymentTypeScriptLanguage,
            DeploymentTypeProcessInfoName,
            DeploymentTypeRequiresUserInteraction,
            DeploymentTypeRequiresLogOn,
            DeploymentTypeUserInteractionMode,
            DeploymentTypePostExecutionBehavior,
            DeploymentTypeExecutionContext,
            DeploymentTypeMachineInstall,
            DeploymentTypeMaxExecuteTime,
            DeploymentTypeExecuteTime,
            DeploymentTypeSlowContentHandlingMode,
            DeploymentTypeFallbackToUnprotectedDP,
            DeploymentTypePinOnClient,
            DeploymentTypeEnabled,
            ScriptResultComparisonValue,
            ScriptRunAs32Bit,
            CreatedAt,
            ModifiedAt,
            CreatedBy,
            ModifiedBy,
            IsDeleted
        )
        SELECT
            Id,
            AppType,
            DeploymentTypeTitle,
            DeploymentTypeTechnology,
            DeploymentTypeDescription,
            DeploymentTypeLanguage,
            DeploymentTypeContentFolder,
            DeploymentTypeUninstallContentFolder,
            DeploymentTypeUninstallContentSetting,
            DeploymentTypeInstallCommandLine,
            DeploymentTypeUninstallCommandLine,
            DeploymentTypeRepairCommandLine,
            DeploymentTypeDetectionScript,
            DeploymentTypeDtScriptJson,
            DeploymentTypeDtIsCustomScript,
            DeploymentTypeScriptLanguage,
            DeploymentTypeProcessInfoName,
            DeploymentTypeRequiresUserInteraction,
            DeploymentTypeRequiresLogOn,
            DeploymentTypeUserInteractionMode,
            DeploymentTypePostExecutionBehavior,
            DeploymentTypeExecutionContext,
            DeploymentTypeMachineInstall,
            DeploymentTypeMaxExecuteTime,
            DeploymentTypeExecuteTime,
            DeploymentTypeSlowContentHandlingMode,
            DeploymentTypeFallbackToUnprotectedDP,
            DeploymentTypePinOnClient,
            DeploymentTypeEnabled,
            ScriptResultComparisonValue,
            ScriptRunAs32Bit,
            CreatedAt,
            ModifiedAt,
            CreatedBy,
            ModifiedBy,
            IsDeleted
        FROM dbo.t_AppDts WITH (HOLDLOCK TABLOCKX)
    ')
GO

DROP TABLE dbo.t_AppDts
GO

EXECUTE sp_rename
    N'dbo.Tmp_t_AppDts',
    N't_AppDts',
    'OBJECT'
GO

ALTER TABLE dbo.t_AppDts ADD CONSTRAINT
    PK_t_AppsDt PRIMARY KEY CLUSTERED
    (
        Id
    )
    WITH
    (
        PAD_INDEX = OFF,
        FILLFACTOR = 90,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
    ON [PRIMARY]
GO

COMMIT

Validation

After the script completes successfully, run the following query:

SELECT
    c.name,
    c.is_nullable
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID('dbo.t_AppDts')
  AND c.name = 'DeploymentTypeUninstallCommandLine';

Expected result:

An is_nullable value of 1 confirms that the field now accepts NULL values and that the fix has been applied successfully.

After validation is complete, restart the Easy2Patch services and retry the relevant operation.

Support

If you encounter any issues while applying the fix, please contact the Easy2Patch support team.