Hi Matthew

That very old mechanism was necessary when we had to support a common method of enabling a disconnected client (Sage CRM Solo running on SQLite) and server database that we either MS SQL Server or Oracle.

Since the end of SOLO and support for Oracles new instances of Sage CRM are installed using MS SQL Server Identity as the mechanism for primary key generation.

I think it is worth requesting guidance from support as I think my L3 colleagues will have notes about moving from the old primary key mechanism to identity columns.

The fact that databases created in 'legacy' version of Sage CRM still use the SQL_ID method to generate record IDs is something I have raised a number of times with various people over the years.  The fact that even if a system is running the latest release of Sage CRM you cannot guarantee how it will function is a big issue.  There are certain components which I have tried to install into supported versions of Sage CRM which fail because the databases were created in older versions.

If I remember correctly the training session "Sage CRM: Developer Training (Entities and the Data Model Intro) Part 2 of 18" https://www.youtube.com/watch?v=NTQ3f09ANlw mentions that it is possible to convert a a legacy created database over to using the modern method of generating record IDs but there are no details.  

Should this not be a process which is run as part of the upgrader?  It must be quite difficult for the developers to maintain Sage CRM code as it will have to take into account both methods of record ID generation.  Converting all databases over to using the modern method of record ID generation would benefit everyone.

I have added this into the development backlog.

Has anyone else come across a Sage CRM database created pre 7.2 which does not upgrade to Sage CRM 2024 R2 without error?  Sage R&D have looked at this, just trying to gauge how big an issue this is.

We have one customer who originally started off with a pre 7.2 CRM and now on 2024 R2 and it went through pretty smoothly. (Our others all started on something at least 7.2 or others unfortunately) 

Your question “..7.2 which does not upgrade to Sage CRM 2024 R2 without error..”

What specific errors are you thinking?

You do not want to jump from 7.2 and upgrade to 2024 R2

The system in question is currently running Sage CRM 2021 R2.2 1220 but was originally implemented with Sage CRM 6.0 I believe.

Following Sage's recommended upgrade path I'm first upgrading to Sage CRM 2024 R1 then upgrading to Sage CRM 2024 R2.  It is the upgrade to Sage CRM 2024 R2 which fails with an error

Cannot insert the value NULL into column 'Libr_LibraryId'

The upgrade is failing I assume as the upgrader is not expecting the database to use the old style stored procedure to generate the record IDs.  This way of assigning record IDs was changed with the release of Sage CRM 7.2 when the solo feature was dropped.  For databases created in 7.2 or later a different method is used to generate record IDs but that change was never retrospectively applied to older databases.

The specify issue appears to relate to the upgrader not being able to create the new templates which are included with Sage CRM 2024 R2, which is not a vital feature.  But the fact that the upgrader does not take older databases into account could potencially lead to other issues.

The resolution should be to migrate older databases over to using the new method of assigning record IDs, which would reduce the issue of upgrade failures in the future.  Sage have listed this is issue CRMS-1945.

No database triggers on the Communication / Library table? 

This the oldIDs is an issue and something I raised awhile ago about some tool or something that would be run against the database to switch from the oldIds to SQL ID, but there is nothing that can do this currently. 

The system I am attempting to upgrade has very little in the way of customisations so any database triggers have been created by Sage.  My expectation was that the this system would just upgrade and that the test upgrade was just a formality. 

Before upgrading a live system I always perform a test upgrade as over the years I have seen many unexpected issues during a test upgrade.  If a live upgrade goes wrong then it can be an absolute nightmare to fix.  Finding and resolving issues in a test environment first is less stressful for me and results in less downtime, so everyone wins.

This system is interesting as it originally used the AIS integration with Sage 200 (if anyone remembers that) which was converted over to use Sage 200 CRM which has now been migrated over to Qnect 200.   I thought that if something was going to go wrong during the test upgrade I was expecting the issue to be around the integration, but the new library templates are standard Sage CRM functionality.

Good point, the installer for 2024 R2 introduces new templates, whilst standard functionality it wants to create new records and thus can't get the IDs for them. 

So I could see the Library error coming up, but that doesn't explain your original comment on the Quotes converted to Orders - that just needs the Trigger looked at. Possibly for the SQL_Identities table looked at to make sure the next number is valid and not already in use. 

Admittedly the customer who worked for us over time got to 2023 R2, and from there upgraded to 2024 R1 then R2. So I have never jumped right from 2021 to 2024. 

There is a wider issue with Sage CRM in that sometimes changes are only applied to new implementations.  The record ID issue is not the only example of this.  A number of years ago Sage implemented the new Relationships tab to replace the old Related tab,  however there was no process to migrate the data from the Related tab to the Relationships tab.  This has also happened with the Library entity when new implementations of Sage CRM have the ability to set permission against the Library entity, whereas upgraded systems do not.

With regard to the original comment about triggers

But, is anyone aware of a way to change CRM from using the old method to actual SQL IDs? As currently the old method is causing some problems in a newer CRM system (converting Quotes to Orders fails due to a trigger on the Order table, but converts OK if the trigger is disabled) the cause is reported as 'failed to get next id' 

The system I am trying to upgrade has the following triggers

Whereas my test environment has the following triggers

Both systems are integrating with Sage 200 via Qnect 200, the difference is that my test environment was implemented with Qnect 200 whereas the client's system has had the AIS and the Sage 200 CRM integrations previously.

All of our customers are Sage 200 customers, so the one that went smoothly for us is one that use the original 'Classic Integration', though we have no customer old enough to have used AIS though. 

So I think the migration (if you followed Qmulus steps) from Classic to Qnect might be a red herring in this case, as we have other customers who were pre-7.2 (+Classic Int) who have all upgraded and migrated to CRM 2000+ without issue, we just have very few on 2024 due to original bugs in R1. 

So if anything it's possibly more an issue in the latest CRM upgrade rather CRM being post 7.2. As you obviously got to 2021 without problems? 

But in answer to your original question 

"... is anyone aware of a way to change CRM from using the old method to actual SQL IDs? ..." 

The answer is no, I have asked this directly of Sage and development have come back and said there is no method / tool that will convert a system from using the SQL_Identities table to pure SQL IDs 

So for your issue, this will need to be raised with Sage Support if you have not already done so for them to investigate the issue in more depth for you. Previously on upgrades I would disable all triggers (making a note of those that were active or not before hand) before doing any upgrade and then reenable them afterwards, thus to rule out triggers being an issue during an upgrade. 

The difference in your triggers is based on Classic Integration, but shouldn't cause you any issue here. 

Sage are aware of the upgrade issue and have given it the reference CRMS-1945.  I was trying to see how many systems out there were being affected by this to get an idea as to how much priority Sage will place on resolving it.

So far this is the only pre 7.2 system I have tried to upgrade to 2024 R2.  My assumption was that this issue would affect all pre 7.2 systems, but I've not had another pre 7.2 database to test with.  I could install 7.1 with demo data then upgrade it thorough all the multiple versions to see whether or not all pre 7.2 systems are affected, but this seems like a lot of effort to me and would not help to resolve the actual issue with the upgrade.

This is labeled as Enhancement Request

This is labeled as Enhancement Request