Error when running TurboDNN742.sql on DNN 7.3.2

Feb 29, 2016 at 8:42 AM
I get this error when i try to run TurboDNN742.sql

System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.UserProfile' and the index name 'IX_UserProfile_PropertyDefinitionID'. The duplicate key value is (143, 578).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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:28624c1c-5980-4559-8efc-bf235b04a2fb
Error Number:1505,State:1,Class:16

CREATE UNIQUE NONCLUSTERED INDEX [IX_UserProfile_PropertyDefinitionID] ON dbo.[UserProfile]
([PropertyDefinitionID] ASC, [UserID] ASC)
INCLUDE ([ProfileId], PropertyValue)
System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ModuleDefinitions_DesktopModules". The conflict occurred in database "DNNNoah", table "dbo.DesktopModules", column 'DesktopModuleID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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:d5c30677-3c61-464a-8849-390578ab981f
Error Number:547,State:0,Class:16

-- make sure, all constraints are enabled
IF (ServerProperty(N'EngineEdition') != 5) --Check for not running on SQL Azure
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Feb 29, 2016 at 8:46 AM
I see that I have these properties on UserProfile:

Image

Is this of any issue?

Best regards
Vidar
Coordinator
Feb 29, 2016 at 9:35 AM
Vidar,
the problem lies in the data of your website, there are two records in UserProfile table for the same user (578) and Property (with PropertyDefinitionID 143).
You may identify all duplicate records by running the following SQL statement in Host > SQL:
SELECT P.* 
FROM UserProfile P
JOIN (SELECT UserID, PropertyDefinitionID, Count(1) N FROM UserProfile GROUP BY UserID, PropertyDefinitionID HAVING Count(1) > 1) S 
ON P.UserID = S.UserID AND P.PropertyDefinitionID = S.PropertyDefinitionID)
Feb 29, 2016 at 2:15 PM
Thanks! Got it.. But got a new error...

System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ModuleDefinitions_DesktopModules". The conflict occurred in database "DNNNoah", table "dbo.DesktopModules", column 'DesktopModuleID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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:67b41912-1da7-4f49-8a0b-a7b959417856
Error Number:547,State:0,Class:16

-- make sure, all constraints are enabled
IF (ServerProperty(N'EngineEdition') != 5) --Check for not running on SQL Azure
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


Coordinator
Feb 29, 2016 at 3:05 PM
Vidar,

your data integrity seems to be broken.
TurboDNN tries to fix it, but I noticed an oversight in my stored procedure.
I will release an update later tonight. Sorry for the issue,

Sebastian
Coordinator
Mar 1, 2016 at 12:54 AM
I updated the scripts, please download latest version and try again.
Marked as answer by leupold on 3/1/2016 at 12:59 AM