Solved

Changing the default starting Identity in Sage CRM

Posted on 2009-05-20
12
841 Views
Last Modified: 2012-08-13
Hi,

I would like to know how to go about changing the default 6000 start number in Sage CRM for new identity numbers to a custom value. As the related solution shows, I have already attempted altering the NextId value contained in the CRM database table "SQL_Identity" for the custom table in question.

The SQL update goes through Ok and shows the next id for that table as 300 my desired start point. However if I go and run the SP that fires this identity process it returns a value of 6500 and the Id_Next value reverts away from 300.

Thanks
0
Comment
Question by:TartanTaurus
  • 8
  • 4
12 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429822
Hi,

This is down to the rep_ranges issues I suspected may be an issue. I'll need to have a read of the sp that generates the ID's in Sage CRM and figure out what values to set and in what table.

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429897
Hi,

I'll assume the table that you wish to change the starting ID for is SocialWorkRegion.

Runs this SQL and post back the results

select * from rep_ranges where range_tableid = (select bord_tableid from custom_tables where bord_caption = 'SocialWorkRegion')

Cheers,

Lee



0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24429901
Sorry, replace bord_caption with bord_name. It shouldn't make a difference but it can if you have changed the name of the table in translations.

Cheers,

Lee
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430064
Hi,

I want to alter the start number of the number we generated in the previous thread (I believe this comes from the "CustomRefs" table we created. I've referenced this table in the SQL and this is the return:
All start Range_:
TableId  RangeStart   RangeEnd   NextRangeStart  NextRangeEnd  Control_NextRange
10169	6500	   6999	      7000	   7099	               7100

Open in new window

0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 24430249
Ok, the ID you want to start at, 300, must be between RangeStart and RangeEnd. To make this work ensure that noone is logged is as you don't want changes occurring while you alter the table values. The next stage is to back up the database. Trust me on this. These tables are not ones you wish to nail. I know, I've done it. I ended up spending several hours writing some SQL code to regenerate the SQL_Identity table as well in case anything ever happened to it again.

So, you've backed up your database. The next thing to do is to alter the value in the SQL_Identity table.

update sql_identity set id_nextid = 300 where id_tableid = 10169

I'm assuming the table ID from your response above. Please ensure you're updating the correct entry.

Next, you need to fix the rep_ranges table.

update rep_ranges set range_rangestart = 1, range_rangeend = 999, range_nextrangestart = 1000, range_nextrangeend = 1999, range_control_nextrange = 2000 where range_tableid = 10169

That should do it.

Cheers,

Lee
0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430452
First class, thanks very much! I will accept that as the solution.

The value returned from the SP is now in the format of AB-CD-00300. How can I change that line to lose the additional 0's I only need to see AB-CD-300.

This is the line in question:
select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)

I can only see 4 0's yet there's 5 appear in the output...thought I'd better ask than destroy the SP!

Thanks
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430662
Change:

select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + right('0000' + cast(@iCustomId as nvarchar(5)), 5)

to

select @nvchCustomCode = @nvchPersInitials + '-' + @nvchRegionCode + '-' + cast(@iCustomId as nvarchar(5))

Cheers,

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430678
5 digits appeared because I was creating a string in the form "0000" + CustomId which would give "0000300". The right() function in SQL was telling it to return the right 5 characters leaving "00300". It's a trick for displaying leading zeros in SQL.

Lee


0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24430886
That's done the trick alright! Thanks very much for all your help, much appreciated!

Best regards,

Chris
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24430890
Any time :)

Lee
0
 
LVL 1

Author Comment

by:TartanTaurus
ID: 24431039
Is there any way I can contact you on this forum via personal message? I'm not going to request help or anything that would break forum rules! I just have a question I'd like to ask away from the public forum :)

Thanks

Chris
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 24439518
I don't think EE has a private messaging system. There is only one other Sage CRM forum on the web that is public which has recently taken a bit a nosedive as it has lost a years worth of posts. I'm on there. If you search the web for Sage CRM forum you'll most likely find it. It does have some useful info on there in general.

Cheers,

Lee
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Review MS SQL cluster diagram 9 89
spx for moving values to new table 5 74
SQL Server Degrading on Write 13 67
How can i get data when i use where clause with group by? 3 36
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question