Can someone tell me where the IDGEN value in SQL Server can be found?

If you think that something doesn't work in Aware IM post your message here
Post Reply
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

Can someone tell me where the IDGEN value in SQL Server can be found?

Post by MarkP »

Hi all,

Urgent!! I'm getting duplicate index errors and I see from previous posts that I need to re-set the IDGEN value to a higher number.
But... I cant find BAS_IDGEN or just IDGEN anywhere in my SQL Server databases. Not even BASDB or BASDBTEST.
Can anyone tell me where it is actually located so I can reset the number please?
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Can someone tell me where the IDGEN value in SQL Server can be found?

Post by PointsWell »

Has your SQL Server been set to use sequences instead of BAS_IDGEN? I remember that Maria and SQL server versions were updated to stop using the IDGEN field for sequences. Can't remember if it is a default or if it is an option.

From reading other questions it looks like it is called BAS_IDGEN_SEQ
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Can someone tell me where the IDGEN value in SQL Server can be found?

Post by customaware »

Assuming you have a seperate DB for the app.... Look for table bas_idgen_seq

You will not be able to directly edit this value.
So,
Park on it and run this following query, replacing 999999999 with the new value you want to set it to.

ALTER SEQUENCE BAS_IDGEN_SEQ RESTART WITH 999999999
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Can someone tell me where the IDGEN value in SQL Server can be found?

Post by Jaymer »

from SSMS, select Properties and adjust values to your need

mssql_sequence.png
mssql_sequence.png (49.1 KiB) Viewed 2278 times
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

Re: Can someone tell me where the IDGEN value in SQL Server can be found?

Post by MarkP »

Fantastic, thanks Jaymer.
That worked. All you need to do then is right click on it and select Properties, tick the Restart box and then the Restart number field becomes editable to let you input any number you like.

And since each database has its own BAS_iDGEN Sequence you can fix an individual database without affecting other databases.
Post Reply