Announcement!
This is a notification for product news or an alert. If you have a question, please start a new discussion

Advisory: Use of deprecated MS SQL Server Datatypes in customizations

Microsoft has deprecated 4 datatypes in SQL Server and has indicated that these will be removed from a future version. 
The deprecated datatypes are:
  • timestamp
  • text
  • ntext
  • image
The deprecation and eventual removal was announced for MS SQL Server 2017.   See:  https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15

The currently supported versions of Sage CRM (Sage CRM Help Center) do not use any of these data types.  However, customers are advised to audit their systems and check customisations for the use of these datatypes.   Customers are advised to use nvarchar(max), varchar(max), and varbinary(max) instead.
Customers can check the datatypes used in an installation using the script below. 
select t.name as data_type,
    count(*) as [columns],
    cast(100.0 * count(*) /
    (select count(*) from sys.tables as tab inner join
        sys.columns as col on tab.object_id = col.object_id)
            as numeric(36, 1)) as percent_columns,
      count(distinct tab.object_id) as [tables],
      cast(100.0 * count(distinct tab.object_id) /
      (select count(*) from sys.tables) as numeric(36, 1)) as percent_tables
  from sys.tables as tab
       inner join sys.columns as col
        on tab.object_id = col.object_id
       left join sys.types as t
        on col.user_type_id = t.user_type_id
group by t.name
order by count(*) desc
 
This applies for Sage CRM stand alone and when integrated with Sage accounting products. Sage 50, Sage 100, Sage 200, Sage 300, Sage X3 and Sage Intacct.