Hi,

I am having a SQL issue when upgrading, please help:

Upgrade

Current Version - 07.03.04

Upgrade - Version 07.04.00

25% ERROR occured - System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.CoreMessaging_NotificationTypeActions' with unique index 'IX_CoreMessaging_NotificationTypeActions_Type'. The duplicate key value is (15, 1). The statement has been terminated. at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:29e8a5ca-cb0a-41a6-9a98-d36362394fa4 Error Number:2601,State:1,Class:14 /* Remove Review action and add Approve, Reject */ DECLARE @NotificationTypeId INT SELECT @NotificationTypeId = NotificationTypeId FROM dbo.CoreMessaging_NotificationTypes WHERE Name = 'ContentWorkflowNotification' IF @NotificationTypeId IS NOT NULL BEGIN /* Delete Review notification action added int 7.3.0 */ DELETE FROM dbo.CoreMessaging_NotificationTypeActions WHERE NotificationTypeId = @NotificationTypeId AND NameResourceKey = 'Review' /* Add Approve notification action */ INSERT INTO dbo.CoreMessaging_NotificationTypeActions( [NotificationTypeID], [NameResourceKey], [DescriptionResourceKey], [ConfirmResourceKey], [Order], [APICall], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID], [LastModifiedOnDate]) VALUES( @NotificationTypeId, 'Approve', 'Approve', NULL, 1, 'DesktopModules/InternalServices/API/ContentWorkflowService/Approve', -1, GETDATE(), -1, GETDATE() ) /* Add Reject notification action */ INSERT INTO dbo.CoreMessaging_NotificationTypeActions( [NotificationTypeID], [NameResourceKey], [DescriptionResourceKey], [ConfirmResourceKey], [Order], [APICall], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID], [LastModifiedOnDate]) VALUES( @NotificationTypeId, 'Reject', 'Reject', NULL, 1, 'DesktopModules/InternalServices/API/ContentWorkflowService/Reject', -1, GETDATE(), -1, GETDATE() ) END System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.CoreMessaging_NotificationTypeActions' with unique index 'IX_CoreMessaging_NotificationTypeActions_Type'. The duplicate key value is (24, 1). The statement has been terminated. at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:29e8a5ca-cb0a-41a6-9a98-d36362394fa4 Error Number:2601,State:1,Class:14 /* Add action to Start Workflow Notification */ DECLARE @NotificationStartWorkflowTypeId INT SELECT @NotificationStartWorkflowTypeId = NotificationTypeId FROM dbo.CoreMessaging_NotificationTypes WHERE Name = 'ContentWorkflowStartWorkflowNotification' IF @NotificationStartWorkflowTypeId IS NOT NULL BEGIN /* Add Submit notification action */ /* also uses the existing Approve method */ INSERT INTO dbo.CoreMessaging_NotificationTypeActions( [NotificationTypeID], [NameResourceKey], [DescriptionResourceKey], [ConfirmResourceKey], [Order], [APICall], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID], [LastModifiedOnDate]) VALUES( @NotificationStartWorkflowTypeId, 'Submit', 'Submit', NULL, 1, 'DesktopModules/InternalServices/API/ContentWorkflowService/Approve', -1, GETDATE(), -1, GETDATE() ) /* Add Discard notification action */ /* also uses the existing Reject method */ INSERT INTO dbo.CoreMessaging_NotificationTypeActions( [NotificationTypeID], [NameResourceKey], [DescriptionResourceKey], [ConfirmResourceKey], [Order], [APICall], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID], [LastModifiedOnDate]) VALUES( @NotificationStartWorkflowTypeId, 'Discard', 'Discard', NULL, 1, 'DesktopModules/InternalServices/API/ContentWorkflowService/Reject', -1, GETDATE(), -1, GETDATE() ) END System.Data.SqlClient.SqlException (0x80131904): Column 'dbo.EventLog.LogEventID' is not the same data type as referencing column 'ExceptionEvents.LogEventID' in foreign key 'FK_ExceptionEvents_LogEventId'. Could not create constraint or index. See previous errors. at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:29e8a5ca-cb0a-41a6-9a98-d36362394fa4 Error Number:1778,State:0,Class:16 IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_ExceptionEvents_LogEventId' AND parent_object_id = OBJECT_ID(N'dbo.[ExceptionEvents]')) BEGIN ALTER TABLE dbo.[ExceptionEvents] WITH CHECK ADD CONSTRAINT [FK_ExceptionEvents_LogEventId] FOREIGN KEY([LogEventID]) REFERENCES dbo.[EventLog] ([LogEventID]) ON DELETE CASCADE END

Hi Sebastian,

Thanks. Did not run it before (intentionally), but I noticed it puts 2 new rows into CoreMessaging_NotificationTypeActions successfully which are related to the new workflow versioning and then it crashes because of a duplicate index called 'IX_CoreMessaging_NotificationTypeActions_Type'.  This index consists of 4 unique combinations of variables. Maybe deleting this index will let me finish with the upgrade. For now I reverted to 07.03.04. In my opinion this script should check before inserting new rows. I don't even know the name of that script, do you know where it is located so that I can maybe improve it?

Hi Sebastian,

Thanks again, I was able to fix the duplicate index problem by making 2 changes to 07.04.00.sqldataprovider. See comments /* Fred.. */   below:

/* Remove Review action and add Approve, Reject */
DECLARE @NotificationTypeId INT
SELECT @NotificationTypeId = NotificationTypeId
    FROM    {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypes
    WHERE   Name = 'ContentWorkflowNotification'
IF @NotificationTypeId IS NOT NULL
BEGIN
 /* Delete Review notification action added int 7.3.0 */
 DELETE FROM {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypeActions WHERE NotificationTypeId = @NotificationTypeId AND NameResourceKey = 'Review'

 /* Add Approve notification action */
    INSERT INTO {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypeActions(
        [NotificationTypeID],
        [NameResourceKey],
        [DescriptionResourceKey],
        [ConfirmResourceKey],
        [Order],
        [APICall],
        [CreatedByUserID],
        [CreatedOnDate],
        [LastModifiedByUserID],
        [LastModifiedOnDate])
    VALUES(
        @NotificationTypeId,
        'Approve',
        'Approve',
        NULL,
        1,
        'DesktopModules/InternalServices/API/ContentWorkflowService/Approve',
        -1,
        GETDATE(),
        -1,
        GETDATE()
        )
  
 /* Add Reject notification action */
 INSERT INTO {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypeActions(
        [NotificationTypeID],
        [NameResourceKey],
        [DescriptionResourceKey],
        [ConfirmResourceKey],
        [Order],
        [APICall],
        [CreatedByUserID],
        [CreatedOnDate],
        [LastModifiedByUserID],
        [LastModifiedOnDate])
    VALUES(
        @NotificationTypeId,
        'Reject',
        'Reject',
        NULL,
        2, /* changed by Fred 2-9-2015 old value 1, */
        'DesktopModules/InternalServices/API/ContentWorkflowService/Reject',
        -1,
        GETDATE(),
        -1,
        GETDATE()
        )
END
GO

/* Add action to Start Workflow Notification */
DECLARE @NotificationStartWorkflowTypeId INT
SELECT @NotificationStartWorkflowTypeId = NotificationTypeId
    FROM    {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypes
    WHERE   Name = 'ContentWorkflowStartWorkflowNotification'
IF @NotificationStartWorkflowTypeId IS NOT NULL
BEGIN
 /* Add Submit notification action */
 /* also uses the existing Approve method */
    INSERT INTO {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypeActions(
        [NotificationTypeID],
        [NameResourceKey],
        [DescriptionResourceKey],
        [ConfirmResourceKey],
        [Order],
        [APICall],
        [CreatedByUserID],
        [CreatedOnDate],
        [LastModifiedByUserID],
        [LastModifiedOnDate])
    VALUES(
        @NotificationStartWorkflowTypeId,
        'Submit',
        'Submit',
        NULL,
        1,
        'DesktopModules/InternalServices/API/ContentWorkflowService/Approve',
        -1,
        GETDATE(),
        -1,
        GETDATE()
        )
  
 /* Add Discard notification action */
 /* also uses the existing Reject method */
 INSERT INTO {databaseOwner}{objectQualifier}CoreMessaging_NotificationTypeActions(
        [NotificationTypeID],
        [NameResourceKey],
        [DescriptionResourceKey],
        [ConfirmResourceKey],
        [Order],
        [APICall],
        [CreatedByUserID],
        [CreatedOnDate],
        [LastModifiedByUserID],
        [LastModifiedOnDate])
    VALUES(
        @NotificationStartWorkflowTypeId,
        'Discard',
        'Discard',
        NULL,
        2, /* changed by Fred 2-9-2015 old value 1, */
        'DesktopModules/InternalServices/API/ContentWorkflowService/Reject',
        -1,
        GETDATE(),
        -1,
        GETDATE()
        )
END
GO

These changes work but I have another issue with the table EventLog. I better post another thread.