Sage CRM

I am getting the following error when adding a new company or person in Sage CRM. An unexpected event has occurred: SQL Code: 2601 SQL Code: 3621 Exception: UnexpectedEvent The SQL Log shows the following: Cannot insert duplicate key row in object 'dbo.Company' with unique index 'IDX_Comp_CompanyId'. The duplicate key value is (50006) I did run the import wizard for MAS, and imported close to 100k records. Curious if anyone has any perspective. Thanks

@JohnWilder - How did the data originally get into CRM? Are you importing directly into MS SQL or via the data upload function? Or did you synch with MAS after importing data into MAS?

Hello Peter, MAS has a relationship wizard that is used to push the current data over to CRM. That is what was used, a synch if you will. Nothing external, so far everything has been system to system using the system tools. Thanks for the note.

So this error is showing up as you are running the synch? Or is this after the synch has been run successfully?

After. The snyc worked just fine (far as I can tell at this point). However, now when we enter a brand new company in CRM, that is when the eror occurs.

Is the duplicate key value always the same? @PeterWolf is there a table in CRM that keeps track of ""next number' data?

Can you pull up the Company database in MS SQL and look at the last id (sort on comp_companyid). Then pull up the SQL_identity table and look for the row with id_tableid = 5 and check the id_nextid column. Can you let me know what those numbers are? The id_nextid value should be one higher than the last Company record's comp_companyid. If it isn't, don't fix it - let me know please.

@PhilMcIntosh - I think I was responding just as you were asking your question. See above.

Here is what I get.. comp_compid = 97134 & id_tableid = 5 is 50008

Yeah... so that would definitely be a problem. Let me confirm with the team as to how to fix it. I know that you will need to set the id_nextid to 97135. Can you also report back as to the various column values in the rep_ranges table for range_tableid = 5?

Range_RangeStart = 50003 Range_RangeEnd = 100002 Next Range Start = 100003 Next Range End = 150002 Control Next range 150003 And thank you @PeterWolf much appreciated.

Well damn... check this article out at Ignite (our monthly online magazine of Sage CRM tips, tricks and strategies). http://ignite.azamba.com/2011/05/update-sql-identity-and-rep-ranges/ Back up your database first.

Great. I'll have to give this a closer look, but it seems to be right on par. Thanks again Peter.

@PeterWolf Thank you! Worked perfectly!

Awesome!