Summary:

Errors may occur when setting up new views to be used by the Sage CRM iPhone app. These errors will prevent users from synching their iPhones with Sage CRM, since data cannot be retrieved from the system.

Symptoms:

On running the component to install the app, you may encounter errors relating to mOppoList_new and mPersonAllDetails. A sample error regarding mOppoList_new is given below:

CREATE VIEW mOppoList_new  AS 
select *,  Oppo_OpportunityId as plink_opportunityid,
    oppo_assigneduserid as plink_oppoprimaryuserid,
    oppo_channelid as plink_oppochannelid,
    oppo_primarycompanyid as plink_oppocompanyid,  ( 
        select  Note_CreatedDate as '@note_createddate',
            Replace( Replace( Replace( Replace(
            Note_Note,
            '<','angbrackLT'),'>','angbrackGT'),'<','angbrackLT'),'>','angbrackGT')
            as '@Note_Note', Note_CreatedBy as '@Note_CreatedBy' 
        from  Notes 
        where  Note_ForeignTableId = (
            select Bord_TableId from Custom_Tables
            where Bord_Name = 'Opportunity') and Note_Deleted is NULL
                and  Note_ForeignId = vo.Oppo_OpportunityId 
                for xml path( 'note' ), root( 'notes' )  ) as plink_oppo_notes 
    from vOpportunity vo
    where oppo_status = 'In Progress'

Argument data type ntext is invalid for argument 1 of replace function



Cause:

The above error refers to an attempt to use an ntext field being used in a REPLACE() function. REPLACE cannot be used with ntext fields. As such, it can be determined that the Note_Note field (which is the subject of the REPLACE) is an ntext field. On checking an un-customised copy of Sage CRM 7.2, you will see that this field should be an nvarchar(max) field.

Resolution:

The issue can be resolved by doing the following:

1: Log all users out of CRM and stop IIS.
2: Take a backup of your Sage CRM database.
3: Run the following SQL:

-- Change the column to nvarchar(max)
alter table Notes alter column Note_Note nvarchar(max)
go
-- Move values that are short enough from the LOB structure back to the table
update Notes set Note_Note = Note_Note
go
-- Reindex the table
dbcc dbreindex (Notes)
go
-- Trigger an index rebuild when CRM restarts
drop view vSentinel
go

4: Start IIS.
5: Log into Sage CRM through a browser.
6: On logging in, the problem views should be rebuilt using the data present on the Custom_Views table.

Status:

It is expected that this issue is caused by the failure of a script to run during the upgrade. Running the above should resolve the issue.

More information:

Further details regarding the ntext and nvarchar data types is available in knowledgebase article 492-16896 on the Sage CRM Community.