Top Secret Trick: How to Change the Auto-Incrementing Value Range

Top Secret Trick: How to Change the Auto-Incrementing Value Range

  • Comments 3
  • Likes

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:

image

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:

image

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:

image

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • It's really a secret trick! Somehow Service Manager still assigning IDs with +2 increment when we create requests from console. Any requests created via Connectors took +1 increment normally. weird behavior!

  • the same Problem as Sadda... Do someone have an idea why it incements always +2?

  • Here is what I need to understand, does the work item id have a set length? Or does it keep increasing to one day max out at 256 character?