This topic has 2 replies, 2 voices, and was last updated 1 month, 1 week ago by Thomas.

  • Author
    Posts
  • #9984
     Thomas
    Member

    I am getting an error on DB_MIGRATE after installing the current version.

    Database (2248, 5/27/2020 3:33 PM, Status: Ok)
    Enterprise Server (2.0.0.2187)
    Portal (2.0.0.2187)
    SRV-XXXX (2.0.0.2187)

     

    The Error:

    00:00:00 Prepare migration.

    Connecting to Db: Server=SRV-XXXX….

    Start migration.

    /* Beginning Transaction */

     

     

    BEGIN TRANSACTION

     

     

    /* 2248: Migration2248 migrating ============================================= */

     

     

     

     

    /* ExecuteSqlStatement

    ALTER PROCEDURE [dbo].[GetPasswordsPaged]

    (

    @FilterColumn nvarchar(50) = ”,

    @FilterValue nvarchar(50) = ”,

    @SortColumn nvarchar(50),

    @StartRow int,

    @MaximumRows int,

    @UserId int,

    @TagType int,

    @TagTypeId int,

    @ShowDeleted bit

    )

    AS

    BEGIN

    EXEC OpenMSPCKey

     

    DECLARE @condition nvarchar(700)

    DECLARE @OrderSortColumn nvarchar(50)

    SET @condition = ‘(@UserId = 0 OR N.UserId = @UserId)’

     

    IF @FilterValue <> ” AND @FilterValue IS NOT NULL

    BEGIN

    IF @FilterColumn <> ” AND @FilterColumn IS NOT NULL

    SET @condition = @condition + ‘ AND ISNULL(dbo.MSPCDecryptString(N.’ + @FilterColumn + ‘), N.’ + @FilterColumn + ‘) LIKE ”’ + @FilterValue + ””

    ELSE

    SET @condition = @condition + ‘ AND ‘ + ‘(ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) LIKE ”’ + @FilterValue + ”’ )’

    END

     

    IF @SortColumn IS NULL OR @SortColumn = ”

    SET @SortColumn = ‘N.CreatedDate DESC’

     

    SET @OrderSortColumn = @SortColumn

     

    DECLARE @fromSql nvarchar(700)

    SET @fromSql = ‘

    FROM Passwords N

    INNER JOIN UsersDecrypted AS U ON U.UserID = N.UserId ‘

     

    IF @TagType > -1 AND @TagTypeId > -1

    BEGIN

    SET @condition = @condition + ‘ AND TagType = @TagType AND TagTypeId = @TagTypeId’

    SET @fromSql = @fromSql + ‘ INNER JOIN TaggedItems AS ti ON N.ID = ti.LinkedItemId AND ti.LinkedItemType = 2 AND ti.TagType=@TagType ‘

    END

     

    DECLARE @sql nvarchar(3500)

     

    set @sql = ‘

    SELECT COUNT(N.ID) ‘ + @fromSql + ‘ WHERE (@ShowDeleted = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

     

    DECLARE @Items AS TABLE

    (

    ID int

    );

     

    WITH TempItems AS (

    SELECT ROW_NUMBER() OVER (ORDER BY N.’ + @OrderSortColumn + ‘) as Row, N.ID ‘

    + @fromSql + ‘ WHERE (@ShowDeleted  = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

    )

     

    INSERT INTO @Items

    SELECT ID FROM TempItems

    WHERE TempItems.Row BETWEEN @StartRow + 1 and @StartRow + @MaximumRows

     

    SELECT

    N.ID,

    N.UserId,

    ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) as Description,

    N.CreatedDate,

    N.IsPrivate,

    N.UpdatedDate,

    U.Username,

    N.CreatedBy,

    N.UpdatedBy,

    N.DeletedAt

    ‘ + @fromSql +’

    JOIN @Items I ON I.ID = N.ID

    ORDER BY N.’ + @SortColumn

     

    print @sql

     

    exec sp_executesql @sql, N’@UserId int, @StartRow int, @MaximumRows int, @TagType int, @TagTypeId int, @ShowDeleted bit’,

    @UserId, @StartRow, @MaximumRows, @TagType, @TagTypeId, @ShowDeleted

     

    EXEC CloseMSPCKey

    END

    GO

     

    ALTER PROCEDURE [dbo].[GetCertificatesPaged]

    (

    @FilterColumn nvarchar(50) = ”,

    @FilterValue nvarchar(50) = ”,

    @SortColumn nvarchar(50),

    @StartRow int,

    @MaximumRows int,

    @UserId int,

    @TagType int,

    @TagTypeId int,

    @ShowDeleted bit

    )

    AS

    BEGIN

    EXEC OpenMSPCKey

     

    DECLARE @condition nvarchar(700)

    DECLARE @OrderSortColumn nvarchar(50)

    SET @condition = ‘(@UserId = 0 OR N.UserId = @UserId)’

     

    IF @FilterValue <> ” AND @FilterValue IS NOT NULL

    BEGIN

    IF @FilterColumn <> ” AND @FilterColumn IS NOT NULL

    SET @condition = @condition + ‘ AND ISNULL(dbo.MSPCDecryptString(N.’ + @FilterColumn + ‘), N.’ + @FilterColumn + ‘) LIKE ”’ + @FilterValue + ””

    ELSE

    SET @condition = @condition + ‘ AND ‘ + ‘(ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) LIKE ”’ + @FilterValue + ”’ )’

    END

     

    IF @SortColumn IS NULL OR @SortColumn = ”

    SET @SortColumn = ‘N.CreatedDate DESC’

     

    SET @OrderSortColumn = @SortColumn

     

    DECLARE @fromSql nvarchar(700)

    SET @fromSql = ‘

    FROM Certificates N

    INNER JOIN UsersDecrypted AS U ON U.UserID = N.UserId ‘

     

    IF @TagType > -1 AND @TagTypeId > -1

    BEGIN

    SET @condition = @condition + ‘ AND TagType = @TagType AND TagTypeId = @TagTypeId’

    SET @fromSql = @fromSql + ‘ INNER JOIN TaggedItems AS ti ON N.ID = ti.LinkedItemId AND ti.LinkedItemType = 2 AND ti.TagType=@TagType ‘

    END

     

    DECLARE @sql nvarchar(3500)

     

    set @sql = ‘

    SELECT COUNT(N.ID) ‘ + @fromSql + ‘ WHERE (@ShowDeleted = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

     

    DECLARE @Items AS TABLE

    (

    ID int

    );

     

    WITH TempItems AS (

    SELECT ROW_NUMBER() OVER (ORDER BY N.’ + @OrderSortColumn + ‘) as Row, N.ID ‘

    + @fromSql + ‘ WHERE (@ShowDeleted  = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

    )

     

    INSERT INTO @Items

    SELECT ID FROM TempItems

    WHERE TempItems.Row BETWEEN @StartRow + 1 and @StartRow + @MaximumRows

     

    SELECT

    N.ID,

    N.UserId,

    ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) as Description,

    N.CreatedDate,

    N.IsPrivate,

    N.UpdatedDate,

    U.Username,

    N.CreatedBy,

    N.UpdatedBy,

    N.DeletedAt

    ‘ + @fromSql +’

    JOIN @Items I ON I.ID = N.ID

    ORDER BY N.’ + @SortColumn

     

    print @sql

     

    exec sp_executesql @sql, N’@UserId int, @StartRow int, @MaximumRows int, @TagType int, @TagTypeId int, @ShowDeleted bit’,

    @UserId, @StartRow, @MaximumRows, @TagType, @TagTypeId, @ShowDeleted

     

    EXEC CloseMSPCKey

    END

    GO

     

    */

     

     

     

    ALTER PROCEDURE [dbo].[GetPasswordsPaged]

    (

    @FilterColumn nvarchar(50) = ”,

    @FilterValue nvarchar(50) = ”,

    @SortColumn nvarchar(50),

    @StartRow int,

    @MaximumRows int,

    @UserId int,

    @TagType int,

    @TagTypeId int,

    @ShowDeleted bit

    )

    AS

    BEGIN

    EXEC OpenMSPCKey

     

    DECLARE @condition nvarchar(700)

    DECLARE @OrderSortColumn nvarchar(50)

    SET @condition = ‘(@UserId = 0 OR N.UserId = @UserId)’

     

    IF @FilterValue <> ” AND @FilterValue IS NOT NULL

    BEGIN

    IF @FilterColumn <> ” AND @FilterColumn IS NOT NULL

    SET @condition = @condition + ‘ AND ISNULL(dbo.MSPCDecryptString(N.’ + @FilterColumn + ‘), N.’ + @FilterColumn + ‘) LIKE ”’ + @FilterValue + ””

    ELSE

    SET @condition = @condition + ‘ AND ‘ + ‘(ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) LIKE ”’ + @FilterValue + ”’ )’

    END

     

    IF @SortColumn IS NULL OR @SortColumn = ”

    SET @SortColumn = ‘N.CreatedDate DESC’

     

    SET @OrderSortColumn = @SortColumn

     

    DECLARE @fromSql nvarchar(700)

    SET @fromSql = ‘

    FROM Passwords N

    INNER JOIN UsersDecrypted AS U ON U.UserID = N.UserId ‘

     

    IF @TagType > -1 AND @TagTypeId > -1

    BEGIN

    SET @condition = @condition + ‘ AND TagType = @TagType AND TagTypeId = @TagTypeId’

    SET @fromSql = @fromSql + ‘ INNER JOIN TaggedItems AS ti ON N.ID = ti.LinkedItemId AND ti.LinkedItemType = 2 AND ti.TagType=@TagType ‘

    END

     

    DECLARE @sql nvarchar(3500)

     

    set @sql = ‘

    SELECT COUNT(N.ID) ‘ + @fromSql + ‘ WHERE (@ShowDeleted = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

     

    DECLARE @Items AS TABLE

    (

    ID int

    );

     

    WITH TempItems AS (

    SELECT ROW_NUMBER() OVER (ORDER BY N.’ + @OrderSortColumn + ‘) as Row, N.ID ‘

    + @fromSql + ‘ WHERE (@ShowDeleted  = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

    )

     

    INSERT INTO @Items

    SELECT ID FROM TempItems

    WHERE TempItems.Row BETWEEN @StartRow + 1 and @StartRow + @MaximumRows

     

    SELECT

    N.ID,

    N.UserId,

    ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) as Description,

    N.CreatedDate,

    N.IsPrivate,

    N.UpdatedDate,

    U.Username,

    N.CreatedBy,

    N.UpdatedBy,

    N.DeletedAt

    ‘ + @fromSql +’

    JOIN @Items I ON I.ID = N.ID

    ORDER BY N.’ + @SortColumn

     

    print @sql

     

    exec sp_executesql @sql, N’@UserId int, @StartRow int, @MaximumRows int, @TagType int, @TagTypeId int, @ShowDeleted bit’,

    @UserId, @StartRow, @MaximumRows, @TagType, @TagTypeId, @ShowDeleted

     

    EXEC CloseMSPCKey

    END

    GO

     

    ALTER PROCEDURE [dbo].[GetCertificatesPaged]

    (

    @FilterColumn nvarchar(50) = ”,

    @FilterValue nvarchar(50) = ”,

    @SortColumn nvarchar(50),

    @StartRow int,

    @MaximumRows int,

    @UserId int,

    @TagType int,

    @TagTypeId int,

    @ShowDeleted bit

    )

    AS

    BEGIN

    EXEC OpenMSPCKey

     

    DECLARE @condition nvarchar(700)

    DECLARE @OrderSortColumn nvarchar(50)

    SET @condition = ‘(@UserId = 0 OR N.UserId = @UserId)’

     

    IF @FilterValue <> ” AND @FilterValue IS NOT NULL

    BEGIN

    IF @FilterColumn <> ” AND @FilterColumn IS NOT NULL

    SET @condition = @condition + ‘ AND ISNULL(dbo.MSPCDecryptString(N.’ + @FilterColumn + ‘), N.’ + @FilterColumn + ‘) LIKE ”’ + @FilterValue + ””

    ELSE

    SET @condition = @condition + ‘ AND ‘ + ‘(ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) LIKE ”’ + @FilterValue + ”’ )’

    END

     

    IF @SortColumn IS NULL OR @SortColumn = ”

    SET @SortColumn = ‘N.CreatedDate DESC’

     

    SET @OrderSortColumn = @SortColumn

     

    DECLARE @fromSql nvarchar(700)

    SET @fromSql = ‘

    FROM Certificates N

    INNER JOIN UsersDecrypted AS U ON U.UserID = N.UserId ‘

     

    IF @TagType > -1 AND @TagTypeId > -1

    BEGIN

    SET @condition = @condition + ‘ AND TagType = @TagType AND TagTypeId = @TagTypeId’

    SET @fromSql = @fromSql + ‘ INNER JOIN TaggedItems AS ti ON N.ID = ti.LinkedItemId AND ti.LinkedItemType = 2 AND ti.TagType=@TagType ‘

    END

     

    DECLARE @sql nvarchar(3500)

     

    set @sql = ‘

    SELECT COUNT(N.ID) ‘ + @fromSql + ‘ WHERE (@ShowDeleted = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

     

    DECLARE @Items AS TABLE

    (

    ID int

    );

     

    WITH TempItems AS (

    SELECT ROW_NUMBER() OVER (ORDER BY N.’ + @OrderSortColumn + ‘) as Row, N.ID ‘

    + @fromSql + ‘ WHERE (@ShowDeleted  = 1 OR DeletedAt IS NULL) AND ‘ + @condition + ‘

    )

     

    INSERT INTO @Items

    SELECT ID FROM TempItems

    WHERE TempItems.Row BETWEEN @StartRow + 1 and @StartRow + @MaximumRows

     

    SELECT

    N.ID,

    N.UserId,

    ISNULL(dbo.MSPCDecryptString(N.Description), N.Description) as Description,

    N.CreatedDate,

    N.IsPrivate,

    N.UpdatedDate,

    U.Username,

    N.CreatedBy,

    N.UpdatedBy,

    N.DeletedAt

    ‘ + @fromSql +’

    JOIN @Items I ON I.ID = N.ID

    ORDER BY N.’ + @SortColumn

     

    print @sql

     

    exec sp_executesql @sql, N’@UserId int, @StartRow int, @MaximumRows int, @TagType int, @TagTypeId int, @ShowDeleted bit’,

    @UserId, @StartRow, @MaximumRows, @TagType, @TagTypeId, @ShowDeleted

     

    EXEC CloseMSPCKey

    END

    GO

     

     

     

     

     

     

    INSERT INTO [dbo].[Versions] ([Version], [BuildDate], [DatabaseVersion]) VALUES (2248, ‘2020-05-27T20:33:34′, N’Documentation: grid search fix’)

     

     

    /* Rolling back transaction */

     

     

    ROLLBACK TRANSACTION

     

     

    ROLLBACK TRANSACTION

     

    00:00:00 Can’t Migrate Up. Violation of PRIMARY KEY constraint ‘PK_Versions’. Cannot insert duplicate key in object ‘dbo.Versions’. The duplicate key value is (2248).

    The statement has been terminated.An error occured executing the following sql:

    INSERT INTO [dbo].[Versions] ([Version], [BuildDate], [DatabaseVersion]) VALUES (2248, ‘2020-05-27T20:33:34′, N’Documentation: grid search fix’)

    The error was Violation of PRIMARY KEY constraint ‘PK_Versions’. Cannot insert duplicate key in object ‘dbo.Versions’. The duplicate key value is (2248).

    The statement has been terminated.

     

    at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)

    at FluentMigrator.Runner.VersionLoader.UpdateVersionInfo(Int64 version, String description)

    at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction)

    at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion, Boolean useAutomaticTransactionManagement)

    at MSPControl.EnterpriseServer.Database.MigrationManager.DoMigration(String connectionString)

    0
    Be the first one to like this.
    Please wait...
    #9985
     Alexander
    Member

    We don’t have such issue.

    1. Please open Versions database table and remove row with Version = 2248

    2. Restart Enterprise Server app pool

    0
    Be the first one to like this.
    Please wait...
    1 user thanked author for this post.
    #9986
     Thomas
    Member

    I deleted the row and restarted the app pool. The logs show no issues this time.

    0
    Be the first one to like this.
    Please wait...
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

©2020 MSPControl | Privacy Policy

Log in with your credentials

or    

Forgot your details?

Create Account