Summary:

One possible way of fixing broken dashboards after an upgrade to Sage CRM 7.1 or 7.2

Symptoms:

The dashboard gadgets are not working, some mail merge features may not be working and some reporting functions may be giving errors.
The logs may show errors like the following:

ERROR [http-apr-10009-exec-75]: 28-May-2014 06:31:31.872 java.lang.NullPointerException
java.lang.NullPointerException
at com.sage.scrm.model.layout.LandingPageStore.isAccessible(LandingPageStore.java:536)
at com.sage.scrm.model.layout.LandingPageStore.getDataSource(LandingPageStore.java:1584)
at sun.reflect.GeneratedMethodAccessor420.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
at com.sun.proxy.$Proxy94.getDataSource(Unknown Source)
at com.sage.scrm.model.store.landingPage.SCRMGetWorkspaceStore.getGadget(SCRMGetWorkspaceStore.java:570)
at com.sage.scrm.model.store.landingPage.SCRMGetWorkspaceStore.getGadgetAndData(SCRMGetWorkspaceStore.java:629)
at com.sage.scrm.model.store.landingPage.SCRMGetWorkspaceStore.getData(SCRMGetWorkspaceStore.java:174)

And

ERROR [http-apr-10009-exec-43]: 28-May-2014 06:31:32.241 Number of entity instance not found in data from the server
ERROR [http-apr-10009-exec-32]: 28-May-2014 06:31:32.282 Parameter id is missing from paramaters for action GET_RECORD_ACTIONS

You can sometimes see whitespace in the logs where there shouldn’t be when the dashboard is loading, and a fiddler trace for when the dashboard is loading (in the XML tab) has spaces like those shown below.

Description

Cause:

Sometimes in some upgrades, column types get changed from ntext to nvarchar (or nchar to nvarchar) and there is some trailing whitespace remaining as the indexes stopped the rtrim that usually is performed.

Resolution:

The two scripts shown below should be executed. They will remove the whitespace after the columns.

(Please change the USE [CRM Install Name] to the install name of the database to be changed

Script 1:

====================================================================================================================
USE [CRM Install Name]

GO

DROP INDEX [IDX_Bord_Caption] ON [dbo].[Custom_Tables]

GO

ALTER TABLE [dbo].[Custom_Tables] DROP CONSTRAINT [UC_CustomTables_Name]

GO

alter table custom_tables alter column bord_caption nvarchar(40)

go

alter table custom_tables alter column bord_name nvarchar(40)

go

CREATE NONCLUSTERED INDEX [IDX_Bord_Caption] ON [dbo].[Custom_Tables]

(

       [Bord_Caption] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Custom_Tables] ADD  CONSTRAINT [UC_CustomTables_Name] UNIQUE NONCLUSTERED

(

       [Bord_Name] ASC,

       [Bord_DatabaseID] ASC,

       [Bord_Deleted] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

update custom_tables set bord_caption = RTRIM(bord_caption),  bord_name = RTRIM(bord_name)

go

============================================================================================================================

 

Script 2:

============================================================================================================================

USE [CRM Install Name]
GO

/****** Object:  Index [IDX_GriP_GridName_ColName_Order]    Script Date: 03/04/2014 08:35:43 ******/

DROP INDEX [IDX_GriP_GridName_ColName_Order] ON [dbo].[Custom_Lists]

GO

/****** Object:  Index [IDX_SeaP_SearchBoxName_ColName]    Script Date: 03/04/2014 08:36:03 ******/

DROP INDEX [IDX_SeaP_SearchBoxName_ColName] ON [dbo].[Custom_Screens]

GO

alter table custom_lists alter column Grip_ColName nvarchar(40)

go

alter table custom_screens alter column SeaP_ColName nvarchar(40)

go

/****** Object:  Index [IDX_GriP_GridName_ColName_Order]    Script Date: 03/04/2014 08:35:43 ******/

CREATE NONCLUSTERED INDEX [IDX_GriP_GridName_ColName_Order] ON [dbo].[Custom_Lists]

(

       [GriP_GridName] ASC,

       [GriP_ColName] ASC,

       [GriP_Order] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object:  Index [IDX_SeaP_SearchBoxName_ColName]    Script Date: 03/04/2014 08:36:03 ******/

CREATE UNIQUE NONCLUSTERED INDEX [IDX_SeaP_SearchBoxName_ColName] ON [dbo].[Custom_Screens]

(

       [SeaP_SearchBoxName] ASC,

       [SeaP_ColName] ASC,

       [Seap_DeviceID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

 

update custom_lists set GriP_ColName = RTRIM(GriP_ColName)

go

update custom_screens set Seap_ColName = RTRIM(Seap_ColName)

go

============================================================================================================================================

More information:

If this does not solve the issue, there may be other underlying problems, but you should now be getting different error messages so the logs should be looked at again.