Summary:

After an upgrade from Sage CRM version 7.1 to version 7.2, the following errors are shown in the Table.esChange.html log:

Errors 8 Items

After line 1010: SQLError : The ' IDX_email_deleted ' type of index object is dependent on column ' Emai_EmailAddress ' ( ALTER TABLE ALTER COLUMN email Emai_EmailAddress NVARCHAR ( 255 ) NULL )
After line 1070: SQLError : The ' IDX_Acty_LogoutMethod ' type of index object is dependent on column ' acty_LogoutMethod ' ( ALTER TABLE ALTER COLUMN Activity acty_LogoutMethod NVARCHAR (40 ) NULL )
After line 1070: SQLError : The ' IDX_Acty_SID ' type of index object is dependent on column ' acty_SID ' ( ALTER TABLE ALTER COLUMN Activity acty_SID NVARCHAR (20 ) NULL )
After line 1135 : SQLError : The ' IDX_Coch_ActionID ' type of index object is dependent on column ' Coch_ActionID ' ( ALTER TABLE ALTER COLUMN CoachingCaptions Coch_ActionID NVARCHAR (60 ) NULL )
After line 2340 : SQLError : The ' IDX_Lock_TableId_RecordId ' type of index object is dependent on column ' lock_Recordid ' ( ALTER TABLE ALTER COLUMN Locks lock_Recordid NVARCHAR (50 ) NOT NULL)
After line 240 : SQLError : The ' IDX_ReSe_ReportSearchName ' type of index object is dependent on column ' ReSe_SearchName ' ( ALTER TABLE ALTER COLUMN Custom_ReportSearches ReSe_SearchName NVARCHAR (50 ) NULL )
After line 565: SQLError : The ' IDX_DData_ShortStr ' type of index object is dependent on column ' DData_ShortStr ' ( ALTER TABLE ALTER COLUMN DData DData_ShortStr NVARCHAR (250 ) NULL )
After line 65: SQLError : The ' IDX_Bord_Caption ' type of index object is dependent on column ' Bord_Caption ' ( ALTER TABLE ALTER COLUMN Custom_Tables Bord_Caption NVARCHAR (40 ) NOT NULL)

This file is located in the directory: C:\Program Files (x86)\Sage\[Install Name]\Setup\esLogs

Cause:

The cause of these errors is that the indexes could not be dropped as they are dependent on certain fields that need their data type updated.

Resolution:

The solution to these issues is to the drop the indexes, update the field data types and then recreate the index.

The steps below  explain the best way to do this:

1. Take a full backup of the database before proceeding.

2. For this example I will use the first error in the logs as the example but you should be able to follow the same steps for each error:

After line 1010: SQLError : The ' IDX_email_deleted ' type of index object is dependent on column ' Emai_EmailAddress ' ( ALTER TABLE ALTER COLUMN email Emai_EmailAddress NVARCHAR ( 255 ) NULL )

3. Open Microsoft SQL Server Management Studio.

4. Select the database you are using for CRM.

5. Select Tables for your database:


 Description

6. In this case we need to use the email table and select the indexes folder:


Description 
 
7. Now we need to script out the indexes. The index can be identified from the error message (highlighted below):

After line 1010: SQLError : The ' IDX_email_deleted ' type of index object is dependent on column ' Emai_EmailAddress ' ( ALTER TABLE ALTER COLUMN email Emai_EmailAddress NVARCHAR ( 255 ) NULL )

In this case it is “IDX_email_deleted”, so we right click on the index “IDX_email_deleted”.

Select the option to Script Index as -> Drop and Create To -> New Query Editor Window.

Description 

You should see the following:

Description 

8. We need to amend this script to so that the data type will be updated:
The query we need to include can be found in the error message (highlighted below):

After line 1010: SQLError : The ' IDX_email_deleted ' type of index object is dependent on column ' Emai_EmailAddress ' ( ALTER TABLE ALTER COLUMN email Emai_EmailAddress NVARCHAR ( 255 ) NULL )

We then need to include this in our script below (highlighted below):

Description 

9. Run this script on the database and then perform an IISReset.

More information:

You can follow the above steps for the other indexes and data types that need to be updated.
Once this is updated to the required data types it should prevent CRM from running into issues with these data types in the future.

WARNING: The techniques or examples contained in this email are for illustrative purposes only, and have not been tested in a production environment. The techniques or examples used are not necessarily supported features of SAGE CRM, and may not have been previously tested or implemented on a production environment. Before attempting to implement any of these techniques or examples on a production environment, they should be tested thoroughly, as use of these techniques may cause unexpected behaviour. SAGE can accept no responsibility for any issues arising from the use of these techniques. Please ensure that you back up your database and application fully before implementing any changes of this nature.