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.
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(
as '@Note_Note', Note_CreatedBy as '@Note_CreatedBy'
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
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.
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)
-- Move values that are short enough from the LOB structure back to the table
update Notes set Note_Note = Note_Note
-- Reindex the table
dbcc dbreindex (Notes)
-- Trigger an index rebuild when CRM restarts
drop view vSentinel
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.
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.
Further details regarding the ntext and nvarchar data types is available in knowledgebase article 492-16896 on the Sage CRM Community.