40FINGERS DNN Nederlands40FINGERS DNN English

Our Blog

Menu: Blog

DotNetNuke Upgrade Issue with 5.4.0 script

By: Timo Breumelhof

Today I tried to upgrade my DNN Skin Development installation from 5.3.1 to 5.6.0
I got a SQL provider error on the 5.4.0 script I did not understand at first.

I got this error in 05.04.00.log.resources

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 
'PK_TabModuleSettings'. Cannot insert duplicate key in object 
'dbo.TabModuleSettings'.
Violation of PRIMARY KEY constraint 'PK_TabModuleSettings'. 
Cannot insert duplicate key in object 'dbo.TabModuleSettings'.
The statement has been terminated.
...
...

I found a Forum thread on the subject, but none of the solutions in there worked for me.

After some research I finally found the reason for the problem.

The SQL that causes the error, sets "hideadminborder" to "True" in the TabModuleSettings table for admin modules.
This prevents the "Only visible by Administrators" message and border from showing up on Regular Admin tabs.

Here's the script:

DECLARE @TabModuleID int
DECLARE CursorQuery CURSOR FOR

SELECT TabModuleID
FROM dbo.TabModules
WHERE (TabID IN(SELECT TabID
FROM dbo.Tabs
WHERE (TabID IN(SELECT AdminTabId FROM dbo.vw_Portals)) OR
(ParentId IN(SELECT AdminTabId FROM dbo.vw_Portals AS vw_Portals_1)) OR (PortalID IS NULL)))

OPEN CursorQuery
---Perform fetch
FETCH NEXT FROM CursorQuery
INTO @TabModuleID
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- now insert the id from the main table into the related table.
            -- hard code values for now for some options.
            -- This is executed as long as the previous fetch succeeds.
        INSERT INTO dbo.TabModuleSettings (
			TabModuleId,
     			SettingName,
			SettingValue,
			CreatedByUserID,
			CreatedOnDate,
			LastModifiedByUserId,
			LastModifiedOnDate
			) VALUES (
			@TabModuleID,
			'hideadminborder',
			'True',
			-1,
			GetDate(),
			-1,
			GetDate())

 FETCH NEXT FROM CursorQuery
 INTO @TabModuleID

END

CLOSE CursorQuery
DEALLOCATE CursorQuery 

I found out on one portal I had 2 Admin modules that already had this setting, causing the PK error of the SQL script.
After I removed them the script ran fine.
I'm not sure how these modules got the setting, but I did create the portal using a portal template from another installation (without content).

Here's some SQL to check if you have the issue.
The test is only valid for DNN 5.3.1 or below.

Use these scripts at your own risk and backup your database before you run them!

Open: Host > SQL
 

You get a list of potential pages with:

SELECT     Tabs.TabName, Tabs.TabID, PortalId
FROM         Tabs INNER JOIN
                      TabModules ON Tabs.TabID = TabModules.TabID INNER JOIN
                      TabModuleSettings ON TabModules.TabModuleID = TabModuleSettings.TabModuleID
where  (SettingName = 'hideadminborder' and SettingValue = 'True')

If you get any Pages listed, these will most likely cause the SQL script to fail.

With the following script you can remove the TabModuleSettings for these page (which should be re-injected with the update script)

Delete from TabModuleSettings where
settingName = 'hideadminborder' and SettingValue = 'True'

After I ran the previous script, my upgrade ran without issues.

I guess there will not be a lot of people with this issue, but I hope this post helps if somebody does.

Categories: General