Reset ID in a JOB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reset ID in a JOB

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-01-03 : 06:02:36

Happy New Year!!!

Every year on the first of January I have to manual reset ID's that create serial numbers and that are built on the running year. (eg. 120000 for this year by truncating the last 6 digits)

In order to automize this process I tried a JOB with this command

DBCC CHECKIDENT('database', RESEED, year (getdate())*10000)

while
select year (getdate())*10000) gives the correct result, inserting it into the DBCC CHECKIDENT throws a syntax error. It seems that the command wont accept any expression to specify the reset-number.

Anybody knows how to accomplish this?
Martin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 06:19:31

put the expression in a variable and use it

ie like


DECLARE @YearVal int
SET @YearVal=year (getdate())*10000
DBCC CHECKIDENT('database', RESEED, @YearVal)

i didnt understand why you're reseeding it each year though. do you remove the data for previous years before you do this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-01-03 : 07:19:23

Thank you visakh, that was the way it finally worked!

The data is not removed because the ID remains unique! We use this to create the running serial numbers for incomming repair orders and it is usefull to have this based on the year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 07:33:05

ok...but what guarantee you've that data in a year wont overflow and make generated id overshoot to next years value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-01-03 : 08:28:41

ok... I see what you mean. Well, experience shows that there wont be more than 3000 cases of RMA cases (returned defect material) in one year... once we have more than 9999 then our firm will have to close anyway ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 09:39:38

hmm..ok...But is it worth taking risk?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-01-03 : 10:27:49

well, whenever you define a standard for a serial code with x-digits you have that same risk. I'm using 4 digits and place the year in front limiting the system to 9999. If in the future I see that the 4 digits wont be enough to cover the incidents over one year I'll change to 5 digits by multiplying the following year with 100.000. Thanks for your interest and concerns, anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 10:53:25

ok...if you're confident, fine with that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 14:06:20

quote:
Originally posted by barnabeck

ok... I see what you mean. Well, experience shows that there wont be more than 3000 cases of RMA cases (returned defect material) in one year... once we have more than 9999 then our firm will have to close anyway ;)

YOU..are gonna have a short-lived career

DBA's are born pessimists.

What's the harm in planning for the worst possible case scenario?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 14:07:27

AND...making "smart" numbers is equally as bad...ok, maybe not equally

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources