Summary:

A HTTP 500 may be returned on running the Swiftpage nightly synch task. This can result in a failure to synchronise the results of nightly batch processing.

Symptoms:

The following errors may be logged:

spnighttask.log:

com.sage.scrm.syncengine.emarketing.communication.handler.drip.SyncCampaignResultsHandler.handleRequest com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://localhost:10009/crmj/$service/saveCampaignResults failed: 500

scrm.log:

ERROR [http-apr-10009-exec-10]: 09-Nov-2015 15:16:45.791 com.sage.scrm.model.error.SageEmarketingException: java.lang.NullPointerException
com.sage.scrm.model.error.SageEmarketingException: java.lang.NullPointerException
 at com.sage.scrm.model.emarketing.SaveCampaignResultsManager.saveCampaignResults

These errors indicate an issue making a call to saveCampaignResults in the CRMJ webapp.



Cause:

Enabling debug logging for this web pp will result in additional data being logged regarding the error:

INFO  [http-apr-10009-exec-8]: 25-Nov-2015 00:04:03.132 processPayload start
DEBUG [http-apr-10009-exec-8]: 25-Nov-2015 00:04:03.132 Set account info content:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<AccountInfo>
       <AccountName>HWalker</AccountName>
       <Users> (User details)

</AccountInfo>

INFO  [http-apr-10009-exec-8]: 25-Nov-2015 00:04:03.163 processPayload before getAccountStatus
INFO  [http-apr-10009-exec-8]: 25-Nov-2015 00:04:03.179 processPayload before updateDBWithReceivedData
INFO  [http-apr-10009-exec-8]: 25-Nov-2015 00:04:03.319 processPayload end
DEBUG [http-apr-10009-exec-6]: 25-Nov-2015 00:04:59.377 saving campaign results for user com.sage.crm.core.data.hibernate.User@7c1 communication Id: 2432006
DEBUG [http-apr-10009-exec-6]: 25-Nov-2015 00:04:59.377 just executed
DEBUG [http-apr-10009-exec-6]: 25-Nov-2015 00:04:59.392 populaterecipients data
DEBUG [http-apr-10009-exec-6]: 25-Nov-2015 00:04:59.486 save comm link 50023
ERROR [http-apr-10009-exec-6]: 25-Nov-2015 00:04:59.517 java.lang.NullPointerException
java.lang.NullPointerException
       at com.sage.scrm.model.emarketing.SaveCampaignResultsManager.saveCommLink(SaveCampaignResultsManager.java:540)
       at com.sage.scrm.model.emarketing.SaveCampaignResultsManager.populateRecipientsData(SaveCampaignResultsManager.java:294)
       at com.sage.scrm.model.emarketing.SaveCampaignResultsManager.saveCampaignResults(SaveCampaignResultsManager.java:101)
       at com.sage.scrm.model.emarketing.SaveCampaignResultsManager$$FastClassByCGLIB$$63032a1e.invoke(<generated>)

       at com.sage.scrm.model.emarketing.SaveCampaignResultsManager$$EnhancerByCGLIB$$b41d1c6.saveCampaignResults(<generated>)
       at com.sage.scrm.model.store.SaveCampaignResultsStore.getData(SaveCampaignResultsStore.java:49)
       at com.sage.scrm.controller.emarketing.AbstractEMarketingController.requestAction(AbstractEMarketingController.java:94)
       at com.sage.scrm.controller.emarketing.SaveCampaignResultsController.requestAction(SaveCampaignResultsController.java:41)

       at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
       at java.lang.Thread.run(Unknown Source)

Thus, the error is raised when saving a communication link. The root cause of this can be determined by looking up the related entities for the communication.

In order to look up the related entity ID (such as a person ID), CRM needs to know what entity an email target is associated with. Each email target (EMarketingEmailTarget) has an entity ID associated with it (EmTa_EntityId). For the record under investigation in this case, the entity ID is 13, which corresponds with the Person table.

A lookup is then done against the Custom_Tables table, looking for a table name that matches a hard-coded value for each entity (Company, Person, etc). If we examine the customer’s Custom_Tables table, we may see that a column on that table has kept an old data type from an earlier version of Sage CRM - in general, you hsould see nvarchar columns rather than nchar and ntext columns.

In the database under investigation in this case, the Bord_Name column was an nchar(40), while Bord_Caption was an nvarchar(40). Nchar columns contain padded data – the DB will generally filter out the right-padded data prior to returning the data from the database. This doesn’t happen for nvarchar columns, since they don’t need to be padded. The extra padding led to a failure within the application.



Resolution:

Once identified, the issue can be resolved using a SQL script.

BEGIN TRAN
-- Remove whitespace from Bord_Caption
update Custom_Tables set Bord_Caption = RTRIM(Bord_Caption)

-- Temporarily drop constraint to allow schema change
ALTER TABLE [dbo].[Custom_Tables] DROP CONSTRAINT [UC_CustomTables_Name]

-- Change Bord_Name to an nvarchar
alter table Custom_Tables alter column Bord_Name nvarchar(40)

-- Remove whitespace
update Custom_Tables set Bord_Name = RTRIM(Bord_Name)

-- Reinstate constraint
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
)
COMMIT TRAN

This should be done when no users are logged onto the system, and should be immediately followed by an IIS reset. It should only be done if it is confirmed that the Bord_Name columns is an nchar(40).