Sage CRM 7.2: Changes to Primary Key Management in MS SQL Server and impact on Code

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Sage CRM 7.2: Changes to Primary Key Management in MS SQL Server and impact on Code

  • Comments 8
  • Likes

Sage CRM 7.2 will introduce new Mobile Apps for Windows 8 and the iPhone.   Please see the article "What's Coming in Sage CRM 7.2?".  As part of preparing for this new way of providing offline content, in an increasingly mobile world, the older style SOLO disconnected client has been dropped.

In Sage CRM v7.1 and earlier the SOLO clients used to use a database called SQL Lite for the offline storage of data.  Because synchronization of data on the client and the server with SOLO needed to work with both Oracle and MS SQL Server databases there had to be a common way of handling the primary key values and the allocation of data ranges so that records created in the SOLO client wouldn't conflict with records created on the server.

You may have investigated tables like rep_ranges, rep_deletes and rep_conflicts when working with Sage CRM v7.1.  These tables are now redundant along with the tables soloprofileoptions and clientrangesrequired and all these tables have been dropped in Sage CRM 7.2.  I will be covering more about the data model changes introduced in Sage CRM v7.2 in a later article.

When Sage CRM v7.1 was installed on MS SQL Server, new IDs for records were generated using a stored procedure called eware_get_identity_id and system tables in the CRM database would store the next id for each table and manage the range information.

But new installs of Sage CRM 7.2 on SQL Server will now use SQL Identities.  Because of this we should see significant performance improvements.  It is one of the very big advantages of the new version.

You can investigate the use of the identity columns using this example SQL code;


SELECT 
t.TABLE_NAME
,c.COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table'
 

For systems that are upgraded from Sage CRM v7.1 to Sage CRM 7.2 support for SOLO will be lost and the stored procedure,eware_get_identity_id, used for generating the primary key will be changed.  This means that existing upgraded systems will still continue to use the earlier way of maintain primary key integrity.

Note: Oracle

  1. There are NO Changes for Sage CRM 7.2 installed on Oracle.  There is no equivalent stored procedure in Oracle and Sequences will continue to be used to control the identities.
  2. SOLO is completely gone in Sage CRM 7.2 and because replication no longer has to be considered the Rep_ranges tables will have gone for Oracle too.

The API objects in both the ASP and .NET environments and in the web services have been changed internally to ensure that inserts will continue to work.

But problems may arise with add-on code that does a direct insert using eware_get_identity_id with the Query object. 

This is likely to affect ‘hidden’ or automatic rules within Sage CRM such as 

  • Validation Rules
  • Table Level Scripts
  • and some ASP and .NET Application Extensions

For example a component might add some code into a new install of Sage CRM v7.1 that looks like this

 

Sage CRM v7.1

[code language = "javascript"]
var strSQL = "DECLARE @ret int";
strSQL += "EXEC @ret=eware_get_identity_id 'cases'";
strSQL += "INSERT cases";
strSQL += "(case_caseid, Case_Description, Case_PrimaryCompanyId)";
strSQL += "VALUES (@ret, 'abc',10)";
var myQuery = CRM.CreateQueryObj(strSQL,"");
myQuery.ExecSQL()
[/code]

BUT in a new install of Sage CRM 7.2 the code should not refer to the eware_get_identity_id stored procedure as primary key values are now managed by identities.  The code would need to be changed to look like this

Sage CRM 7.2


var strSQL = "INSERT INTO Cases";
strSQL += "(Case_Description, Case_PrimaryCompanyId)"; 
strSQL += "VALUES ('abc',10)")";
var myQuery = CRM.CreateQueryObj(strSQL,"");
myQuery.ExecSQL()

 

Note:  In .NET the code reference to look for is the use of the Sage.CRM.Data.QuerySelect class.

As I mentioned earlier the ASP &.NET Record object has not been effected.

Upgraded vs New Systems

Upgraded System will still use old eware_get_identity_id mechanism.

Code that has to run on upgraded and new installations may need to work out how to do inserts.  If we are creating a component that could be run on an upgraded system on on a new system we should have a way to check in code which primary key system is in use?  We can take advantage of a system  parameter called "StillGotIdentityCols".


select parm_name, parm_value from custom_sysparams where parm_name = 'StillGotIdentityCols';
 

In a vanilla install of Sage CRM 7.2 this will be 'Y' in an install upgraded from Sage CRM v7.1 this will be null.

I hope in the future that there may be a way to move an existing system to use SQL identities instead of the eware procedure.  As you can see the behaviour is partially governed by a system parameter and I know that we have implementations on SQL Server in which the use of the stored procedure has been replaced by Identities.  BUT this is not a trivial matter and I must wait until guidance is officially issued on this.
Comments
  • Jeff,

    How would we go about getting the Identities of the inserted record? If we do the same for a new Company with default Address (or Person), we need to update the Company’s Primary Fields with the Identity Id of that Address…

  • Jaime

    I assume that you want to know how to get the id for a record that has just been created using the CreateRecord method.  Once the record has been saved it is available to us.  The record is saved in a screen when the block execute() method is used e.g.

    CRM.AddContent(myBlockContainer.Execute(contactRecord));

    if (CRM.Mode == Save)

    {

    Response.Redirect(CRM.URL("contacts/contactssummary.asp")+"&companyid="+contactRecord.companyid);

    }

    The code above is an extract from a page that creates a new record in a table called contacts, the primary key is 'companyid' and can be referenced one the record is inserted.

    The secod example is from Table Scripts, consider this

    var myRecord = CRM.CreateRecord("TableName");

    myRecord.firstField= "value";

    myRecord.secondField= "value";

    myRecord.SaveChanges();

    var PK = myRecord.primarykeyField;

    Once the SaveChanges() is called the primarykey for the new record is available for us to reference.

  • Jeff, I have a few MS SQL stored procedures that I use to Migrate other CRM Systems into SageCRM. When I import Communications, I would need to get the comm_communicationid from the newly inserted communication and then use it to create the comm_link record.

    Currently I use: EXEC @retCommID=eware_get_identity_id 'communications'

    EXEC @retCommLinkID=eware_get_identity_id 'comm_link'

    to get the next Communication and Comm_link ID.

    What is the method for SQL Identities?

  • Ken

    If you use the CRM.CreateRecord() method to create a Record object, once the myRecord.SaveChanges() method is invoked the primary key value will be available to you.

  • Any news on:

    "I hope in the future that there may be a way to move an existing system to use SQL identities instead of the eware procedure."

  • I have not seen anything published.

  • I would say it should be possible with a big sql script :

    Maybe a way to do this

    1) create on each v7.1 table a new field which will be a backup of the previous known id of the table (for instance : comp_companyid_bak) and copy the current value on the id into this new "backup" field.

    2) instead of upgrading, make a new install first so that the new SQL Identity behaviour is available with an "empty database" (ie no demo data)

    3) create on each v7.2 table the same fields than in step 1)

    4) populate your tables with insert into newtable () select from oldtable

    5) then you should be able to update all the foreign key relationships between tables thanks to the correspondance between new SQL identity of records with the old id of the same records with a big sql patch, table after table, relationship after relationship.

    I'm pretty sure Sage development team can do this asap !

    This should be a basic DBA job ;)

  • Alas, it is not a straight forward job.