Find and Fix Missing Tab ContentItemIds


DNN version 5.3.0 brought us the dbo.ContentItems table and related changes. One of the related changes was a foreign key column to dbo.ContentItems from the dbo.Tabs table. A lot of code in DNN assumes that the ContentItemId column in dbo.Tabs is not null but, for reasons unknown, after an upgrade the value is null for existing pages. If you just view the settings for a tab then the ContentItemId is written back to the database but if you have hundreds of pages this isn't a viable way to fix the problem.

The first script below will return tabs with a null ContentItemId - if it returns no rows then you don't have this problem.

The second script below creates missing rows in the dbo.ContentItem table then populates the null ContentItemIds in the dbo.Tabs table. The script does not currently make any changes for the host tabs. After using the script you will need to clear the cache (Host Settings, Advanced Settings section, Performance Settings) for the new ContentItemIds to be recognised.

For both scripts you may need to update table references in the script if your DNN database uses non-default {databaseOwner} and {objectQualifier} values.

Originally written 17th July 2010. Scripts provided as-is without warranty, use at your own risk.


Find Missing Tab ContentItemIds Script

   PortalId is not null and
   ContentItemId is null

Fix Missing Tab ContentItemIds Script

begin tran

declare @UserId int; set @UserId = 1  -- CHANGE USERID IF REQUIRED.
declare @d datetime; set @d = getdate()
declare @ContentTypeID int; select @ContentTypeID = ContentTypeID from dbo.ContentTypes where ContentType = 'Tab'
if @@rowcount <> 1 begin raiserror('Failed to read ContentTypeId for "Tab".', 16, 1); rollback; return; end

-- Create the missing ContentItem rows.
insert dbo.ContentItems (Content, ContentTypeID, TabID, ModuleID, ContentKey, Indexed, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate)
   t.TabName as Content, 
   @ContentTypeID as ContentTypeID, 
   -1 as ModuleID, 
   N'' as ContentKey, 
   0 as Indexed, 
   @UserId as CreatedByUserID, 
   @d as CreatedOnDate, 
   @UserId as LastModifiedByUserID, 
   @d as LastModifiedOnDate
   dbo.Tabs t
   t.PortalId is not null
   and t.ContentItemId is null 

if (@@error <> 0) begin rollback; return; end

-- Update the missing ContentItemIds.
update t set
   ContentItemId = ci.ContentItemId,
   CultureCode = p.DefaultLanguage,
   LastModifiedByUserID = @UserId,
   LastModifiedOnDate = @d
   dbo.Tabs t
      inner join dbo.ContentItems ci on ci.TabId = t.TabId
                                    and ci.ContentTypeID = 1
         inner join dbo.Portals p on p.PortalId = t.PortalId
   t.PortalId is not null
   and t.ContentItemId is null 

if (@@error <> 0) begin rollback; return; end

print ''
print 'Committed'
Copyright 2007-2015 RMJCS Ltd