Hello,

I apologize in advance for the long post. I'm setting up SageCRM and integrating it with MAS 200. I'm getting SQL errors 2627 and 3621 when trying to create a new person. I'm running SageCRM version 7.1.d.4 S and MAS 200 4.50.6. I ran the Relationship Management Wizard in MAS 200 to populated SageCRM with all Customers and Companies, Contacts and Ship-To Addresses from MAS 200. The errors occur when creating a person AFTER populating SageCRM with MAS 200 data.

Before manually creating any records in SageCRM, the Address and Address_Link tables contained 66899 records each. The corresponding NextId's in SQL_Identity were in sync, with the value 66900 for ech table. After trying to create a person and getting the errors the NextId for the Address table was changed to 50003. The entry below from the log shows the query is trying to write value 50002 to Addr_AddressId rather than 66900.

Apr 23 2013 10:45:29.395 8900 7760 1 SQL Error : Violation of PRIMARY KEY constraint 'PK__Address__6F664AC67F60ED59'. Cannot insert duplicate key in object 'dbo.Address' (INSERT INTO Address(addr_address1,addr_address2,addr_address3,addr_address4,addr_city,addr_state,addr_postcode,addr_country,addr_CreatedBy,addr_CreatedDate,addr_UpdatedBy,addr_TimeStamp,addr_UpdatedDate,Addr_AddressId) VALUES (N'5151 ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,'20130423 10:45:29',1,'20130423 10:45:29','20130423 10:45:29',50002)

I reproduced this behavior multiple times.

To test further I modified the NextId's in SQL_Identity to make sure they were in sync with the max id's in tables Address, Address_Link, Person, Person_Link, Phone and Phone_Link. I would then try to create a new person record then repeat resyncing the NextId's in the above tables until the problem cycled through most of the tables and the new person record was eventually created. It took five attempts for the queries to cycle through most of the tables trying to write value 50002 to the id fields before the record was created. The only tables the queries didn't try writting 50002 to the id fields were the Person and Person_Link tables, each table had fewer than 50000 records. The Phone and Phone_Link tables had 81114 records each. Below are my notes from each attempt:

1st attempt to create new person
Address: no record written, the next id changed to 50003
Address_Link: no record written, no change to next id
Person: no record written, next id is incremented by 1
Person_Link: no record written, no change to next id
Phone: no record written, no change to next id
Phone_Link: no record written, no change to next id

After resyncing next id's and creating person
Address: no record written, next id incremented by 1
Address_Link: no record written, next id changed to 50003
Person: no record written, next id incremented by 1
Person_Link: no record written, no change to next id
Phone: no record written, no change to next id
Phone_Link: no record written, no change to next id

After resyncing next id's and creating person
Address: no record written, next id incremented by 1
Address_Link: no record written, next id incremented by 1
Person: no record written, next id incremented by 1
Person_Link: no record written, no change to next id
Phone: no record written, next id changed to 50003
Phone_Link: no record written, no change to next id

After resyncing next id's and creating person
Address: no record written, next id incremented by 1
Address_Link: no record written, next id incremented by 1
Person: no record written, next id incremented by 1
Person_Link: no record written, no change to next id
Phone: no record written, next id incremented by 1
Phone_Link: no record written, next id changed to 50003

After resyncing next id's and creating person
Address: record written, next id incremented by 1
Address_Link: record written, next id incremented by 1
Person: record written, next id incremented by 1
Person_Link: no record written, no change to next id
Phone: record written, next id incremented by 1
Phone_Link: record written, next id incremented by 1

I'm at a point now where I can create Person records, but I'm concerned releasing SageCRM to a production environment. Will this issue crop up when creating other records or when the Person tables surpass 50000 records?

Thanks