Error executing TurboDNN742.sql

Oct 19, 2015 at 3:57 PM
Hi,

after upgrading to DNN 7.4.2 i tried to run the script but i get this error:

System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SkinControls_Packages". The conflict occurred in database "DotNetNuke", table "dbo.Packages", column 'PackageID'.
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:3a22af87-3901-4f30-8383-982f3cd85874
Error Number:547,State:0,Class:16

-- make sure, all constraints are enabled

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

What am i wrong ?
Thanks
Coordinator
Oct 20, 2015 at 8:14 PM
this seems to be another issue of DNN integrity, there is a foreign key constraint from Skins table to Packages table, but not for each Skin there is a package and packageID in Skins table is set to -1. I will create a fix, you may simply ignore for now.
Coordinator
Oct 22, 2015 at 7:07 AM
handled in TurboDNN 0.9.9 Rev. h
Marked as answer by leupold on 10/22/2015 at 12:07 AM
Oct 28, 2015 at 9:50 AM
Hi,

i have downloaded the last (m) revision but now i have another erro:

System.Data.SqlClient.SqlException (0x80131904): The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
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:2a78a102-7f46-4f89-be9b-db4065ba0541
Error Number:8672,State:1,Class:16

-- make sure there is a package for each skin and container package
MERGE INTO dbo.[Packages] D
USING (SELECT ControlKey
       FROM  dbo.[SkinControls] 
       WHERE IsNull(PackageID, -1) NOT IN (SELECT PackageID FROM dbo.[Packages] 
                                            WHERE PackageType IN (N'Skin', N'Container', N'Module', N'SkinObject'))) S 
       ON D.Name Like N'%' + S.ControlKey + N'%' AND D.PackageType IN (N'Skin', N'Container', N'Module', N'SkinObject')
 WHEN NOT MATCHED THEN INSERT (PortalID,   Name,   FriendlyName, Description, PackageType, Version, Owner, Organization, Url, EMail, IsSystemPackage, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate) 
                       VALUES (Null, S.ControlKey + N'.Skinobject', S.ControlKey + N' Skinobject',          N'', N'SkinObject', N'1.0.0', N'unkown', N'unkown', N'', N'', 1, -1, GetDate(), -1, GetDate());
-- make sure, package numbers match:
MERGE INTO dbo.[SkinControls] C
USING (SELECT PackageID, Name
      FROM dbo.[Packages] WHERE PackageType Like N'SkinObject') S 
      ON S.Name Like N'%' + C.ControlKey + N'%' 
WHEN MATCHED AND S.PackageID != IsNull(C.PackageID, -1) 
AND IsNull(C.PackageID, -1)  NOT IN (SELECT PackageID FROM dbo.[Packages] 
                                      WHERE PackageType IN (N'Skin', N'Container', N'Module', N'SkinObject'))
THEN UPDATE SET PackageID = S.PackageID;
--
What has been wrong now ?
Thanks
Coordinator
Oct 28, 2015 at 1:29 PM
This indicates bad quality of data in your DNN. I am trying to enable the foreign key constraint (relationship) from Skins (column PackageID) to Packages by creating a packages record for each skin, which doesn't have one. It seems, like you are having multiple skins or containers with same name, is this possible?
Oct 28, 2015 at 6:11 PM
This is strange because in previous versions this error doesn't happened.
Anyway could you suggest to me which table must i check ??
Thanks a lot
Stefano
Coordinator
Oct 28, 2015 at 9:35 PM
it is a new improvement, therefore you might not have faced the issue before.
please check you SkinControls table for 2 rows with same Controlkey value.
Oct 28, 2015 at 9:45 PM
No, i don't have this problem on my SkinControls table:
SkinControlID PackageID ControlKey
41 13 DNN360MENU
36 147 LEFTMENU
37 154 TAGS
38 164 JQUERY
39 165 CONTROLPANEL
40 168 DDRMENU
42 172 JDMENU
43 173 DNNJDMENU
44 189 LINKTOMOBILESITE
45 190 LINkTOFULLSITE
46 191 META
47 274 Toast
This is the result for the query.
Coordinator
Oct 30, 2015 at 3:19 PM
is there a valid entry in table Packages for each of these SkinControls?
SELECT *
FROM Skincontrols C
LEFT JOIN Packages P on C.PackageID = P.PackageID
WHERE P.PackageID Is Null
Oct 30, 2015 at 5:57 PM
Yes, the query return no result.
Coordinator
Oct 30, 2015 at 8:00 PM
Edited Oct 30, 2015 at 8:01 PM
interesting, which of the two merge Statements fail?

PS: don't worry, this issue won't affect function of your Website. it is just a missing improvement.
Nov 10, 2015 at 4:42 PM
hi,
i cannot reach which merge fail, because on the error reporting above are join together.
Stefano