In Service Manager you can define properties as “auto incrementing” meaning that each value should be higher than the next and automatically assigned by the system. One good example of this is the Work Item ID. Each time a work item is created a new Work Item ID is generated which is 1 larger than the previous one.
There is a secret way to change the auto-incrementing value range in the database. I”m not even sure this is really supported so don’t do this in production unless you are a risk taker! :) But it’s great for testing and such things regardless.
Basically there is this table in the ServiceManager database called the AutoIncrementAvailableRange table. This value stores the next available number for a particular class property. If the last work item ID that was handed out was 1234 this table would show 1235 and then once 1235 was used it would say 1236. You get the idea. So, by changing this number we can change the base number that we start from. Let’s say for example that we always want our incident IDs to be in the 10,000+ range. We could change thus number to 10000 and start from there. Definitely don’t set it lower than the current number though! That will most likely be very bad.
So – here’s what you do. First you need to know which row represents the property you want to update. If you just run this:
select * from AutoIncrementAvailableRange
you’ll get this:
Not exactly human readable. So – we need to join a few tables. (sorry in advance for what I’m sure is really bad T-SQL)
select
MT.TypeName,
MT.ManagedTypeId,
MTP.ManagedTypePropertyName,
MTP.ManagedTypePropertyID,
AIAR.FirstAvailableValue
from ManagedType as MT, ManagedTypeProperty as MTP, AutoIncrementAvailableRange as AIAR
where MT.ManagedTypeId = AIAR.ManagedTypeId and MTP.ManagedTypePropertyId = AIAR.ManagedTypePropertyId
That gives us results which are more readable:
So – now let’s say we want to adjust the WorkItem ID to start at 10000. We would just run a query like this:
update AutoIncrementAvailableRange
set FirstAvailableValue = 10000
where ManagedTypeId = 'F59821E2-0364-ED2C-19E3-752EFBB1ECE9' and ManagedTypePropertyId = '28B1C58F-AEFA-A449-7496-4805186BD94F'
Now if I go to create a new incident look at what the ID number is:
