Reseed the identity Column with Trigger.

I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

Thanks for trying.I can't count the number of DBAs and developers who have sworn off triggers, declaring them to be useless and dangerous and outlawing them from any database application.
And why?
Because people do such ridiculous things with them.
Used wisely, triggers are powerful, useful, and reliable.
I do not think you are using them wisely.
Now, instead of explaining the steps you are going through to get from Point A to Point B, tell us what Point A and Point B ARE, and maybe we can help you choose a better path. What you have described so far is definitely from the Rube Goldberg school of database development.|||1. Newly built ASP.Net menu pages, which were required to be built in a matter of weeks, read properly normalized tblUserModules table to determine which users have rights to which modules.

2. Old ASP user registration application and VB 5 RDS exe User Security tools assign users the needed rights using poorly constructed flat file style users table.

3. User registration which was deployed this February after 3 years of construction would take months if not years to convert. User Security tool in process of being converted to web based .Net tool to comply with DOD NMCI requirements but completion date on that is still months away.

4. Problem: Once new menus are deployed, which was supposed to happen 10/11/2005, then new menu will not see any updates to user rights or any new users unless SQL Server illiterates manually open Enterprise manager and add or update records in tblUserModules. In addition, once New Web based user management tool has been deployed, User Registration will not see any updates that have been made to user rights.

5. Solution: Place triggers on the two tables such that when update on one occurs the same update takes place on the other.

This would allow us to:

1. Deploy the new menu pages and later the new user management tool without waiting the months that would be required to update user registration.

2. Begin the process of migrating this site away from the technologies no longer supported by Microsoft such as Design Time Controls, which are used extensively throughout the 2500 pages of this site.

3. Capture information on user updates with recoding the old exe which is no longer permitted by DOD's NMCI policy.

If you know a way to accomplish this that would not require recoding of the exe and delaying deployment until registration has been converted over and that will not delay our deployment any more than a few days I would be glad to hear it.|||None of your systems should be updating the tables directly. They should be updating the tables by calling stored procedures. That's just database design best-practices.
If your business rules were simple I'd say put them in triggers on your tables, but since they are complicated and involve the creation of temporary objects and independent key structures I would encourage you to place this logic in the store procedures used to maintain the data.|||As stated we are in the process of updating the 2500 hundred page web site. None of the systems should be updating tables directly but they do. That what I inherited and thats what has to be lived with until changed. There should be no flat files masquerading as database tables, but there are. Thats what I have to live with until changed. Meanwhile, the site has to continue to operate in order for the WarFighter to get the parts needed to save their lives in case of aircraft malfunction. The older it gets the more malfunctions occur. It is not possible to leave the old site up untouched until all the desirable changes have been developed and tested. In real world situations involving lives of DOD personell, it is only possible to make the updates a section at a time, which means that the old breaking parts must be maintained while the replacements are being developed. I am well aware that neither the inherited databases nor the code structure is optimum or even good. What was asked is how to put baling wire and scotch tape on the system to hold it together while it is being rebuilt.
If you have answers that can address this immediate must do now problem please let me know. I have no time now for "it should have beens".

ERJ
Senior Programmer/Analsyt
MCSD MCDBA|||What you are attempting is probably achievable, but has too many hacks in it to be solved in a few forum postings, or without hours and hours of coding and debugging time. If you insist on doing this (why is there always time to do things wrong?), then I suggest you find and pay a very good SQL consult to write your code. I'm not trying to offend you, but I and other people on this forum are reluctant to spend hours of unpaid time helping people write bad code.|||Fine
I will be sure to tell that to the families of the Marines that die in Iraq because of lack of air support because you were too snobby to dirty you hands to help to provide an immediate temporary fix to a massive poorly designed production system.|||Joeller ... stop being an a**-hole. Blindman told you "... [it] has too many hacks in it to be solved in a few forum postings, or without hours and hours of coding and debugging time ...". This is a truism. People (including DBAs and database developers) expect to be paid for hours and hours (or days and days) of fixing other peoples problems.

Now I see the MCDBA below your initials in your last posting. That means you took and passed the application DBA test too. So stop being so self righteous and trying to shift the blame to a forum instead of designing a workaround or paying for the work to be done.

You suggested a plan-of-attack in your first post to this thread. You were informed that this is not in keeping with "best practices". Don't kill the messenger. You may not like the message, but it is not preventing YOU from fixing the problem, or paying for help to do so. You can even fix it poorly so it will have to be fixed again. You are in charge of your project. Piss poor planing by somebody else does not create an emergency on my part. Nor does it give you the right to try and dump blame on people whom you ask for help when they tell you something you don't want to hear!

I support the US military ... the best fighting force in the world! I spent my time in the Air Force. I worked as a civilian in DP at a Naval shipyard while I got my BA degree. I'm private sector now. You do your job, and we will do ours.

Get to work and stop bitching!|||hi Joeller,

in post #1 of the thread it is mentioned that "The id column of the temporary table will be added to the max value in the final table to create the record identifier". ..and aftr data insertion, u r trying to reseed the identity value of the "temp" table. if the identity increment is 1, and the only purpose of reseeding the identity value, is to make the new "record identifier",the previous one +1, can't u change the code in the 'trigger' to some thing like

get max(recordidentifier) from inserted, say x
add 1 to x
INSERT INTO FINALTABLE SELECT @.X, i.field1,i.field2 from inserted i

i doubt whether something is wrong in my understanding of ur post.

pl discuss|||Fine
I will be sure to tell that to the families of the Marines that die in Iraq because of lack of air support because you were too snobby to dirty you hands to help to provide an immediate temporary fix to a massive poorly designed production system.
I cannot in good conscience allow patriotic Americans to be slaughtered because I helped you write crappy, buggy software. And that will be my testimony before congress, so help me God.

Reseed The Identity Column

Hello there,
I am having problem in modifying the identity column of a table.

Currently the identity column is seeded by 1 with an increment of 1 and DATA with these properties of the column is stored.

Now, I want to reseed the identity column with an increment of 2.

The query I am using is

ALTER TABLE XP ALTER COLUMN XP_ID BIGINT IDENTITY ( 500, 2 ) NOT NULL

Is there any one out there who can help me?Take a look at DBCC CHECKIDENT() in sql book online.|||I have to ask why you want an increment of 2. There are a lot of things that CAN be done in SQL Server that probably SHOULDN'T be done in SQL Server...|||Take a look at DBCC CHECKIDENT() in sql book online.

Thank you very much.

You have told me a nice thing but I reached there before you told me.

Thanks for the solution.|||Some government must want to put a tax on odd numbers, is all I can think of. The price of going international, I suppose ;-).|||I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

Thanks for trying.sql

reseed required after solving conflicts

I have set-up several replications, all of them working on the same
way. But on the last one a strange thing occured: It seems that when a
conflict is solved by choosing to use the displayed data from the
looser, I need to reseed the identity column.
The replication is working on the merge model. On the two databases,
the identity is not for replication, with different seed ( 1 for the
main database, 2 for the other one) and an increment of 10.
The databases are SQL Server 2000, and the operating system is Windows
2000 server.
Could you please tell me why this reseed is required.
Regards
Patrick
Why do you say you need to reseed the identity column? What error message is
generated?
Or is what happens is that subsequent inserts pick the last inserted value?
For example you have an identity range of 10 on one side 1000 on another.
The 1001 row is merged to the publisher but is logged as a conflict and
rolled back. Then you select this conflict to win, and the 1001 row gets
inserted in the publisher. Then does the publisher's next value start at
1002?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick" <pmenage@.intnet.mu> wrote in message
news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...
>I have set-up several replications, all of them working on the same
> way. But on the last one a strange thing occured: It seems that when a
> conflict is solved by choosing to use the displayed data from the
> looser, I need to reseed the identity column.
> The replication is working on the merge model. On the two databases,
> the identity is not for replication, with different seed ( 1 for the
> main database, 2 for the other one) and an increment of 10.
> The databases are SQL Server 2000, and the operating system is Windows
> 2000 server.
> Could you please tell me why this reseed is required.
> Regards
> Patrick
>
|||After solving a confict on a table with an identity, I need to ressed
the identity of this table else, the identity of next record inserted
by the application takes the value just after the one of the identity
of the record on which there were conflicts. As there are other exiting
records, it is rejected due to primary key problem. It seems that when
solving a conflict, the seed is changed to the value of the identity of
the record on wich the conflict was solved.
To answer your second question, yes, the publishers's next value start
at 1002.
I set up the identity to start at 1 on the main database and at 2 on
the other database. On both side the increment is 10.
Looking forward to reading your thoughts on the above.
Best Regards
Patrick
Hilary Cotter wrote:[vbcol=seagreen]
> Why do you say you need to reseed the identity column? What error message is
> generated?
> Or is what happens is that subsequent inserts pick the last inserted value?
> For example you have an identity range of 10 on one side 1000 on another.
> The 1001 row is merged to the publisher but is logged as a conflict and
> rolled back. Then you select this conflict to win, and the 1001 row gets
> inserted in the publisher. Then does the publisher's next value start at
> 1002?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Patrick" <pmenage@.intnet.mu> wrote in message
> news:1160991102.318000.68140@.i3g2000cwc.googlegrou ps.com...

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
Message posted via http://www.droptable.com
DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.droptable.co m...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
Message posted via http://www.droptable.com

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
--
Message posted via http://www.sqlmonster.comDBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.SQLMonster.com...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
--
Message posted via http://www.sqlmonster.com

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
Message posted via http://www.droptable.comDBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.SQ
droptable.com...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
Message posted via http://www.droptable.com

Reseed identity column in SQL 2000

Hello. I see that we're not permitted to reseed a incremental column to a
value that's lower than the highest value in the table. I guess that's in
there for our protection but is there any way around this? On an integer
column I manually set row to a value of 2 billion and had planned to reseed
to a very low number never to approach it. If we have to start at 2 billion
it's possible that we'll run out of room.
Guess I can change the column to a large int. Is anyone thinking that
identity fields are more hassle than they're worth?
TIA,
Ken Trockktrock wrote:
> Hello. I see that we're not permitted to reseed a incremental column to a
> value that's lower than the highest value in the table. I guess that's in
> there for our protection but is there any way around this? On an integer
> column I manually set row to a value of 2 billion and had planned to resee
d
> to a very low number never to approach it. If we have to start at 2 billio
n
> it's possible that we'll run out of room.
> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
> TIA,
> Ken Trock
I think you are referring to this comment in Books Online:
"If the value of new_reseed_value is less than the maximum value in the
identity column, error message 2627 will be generated on subsequent
references to the table."
http://msdn.microsoft.com/library/d...asp?frame=true
This implies that setting a value lower than the maximum won't work but
if you know that error message 2627 is a key violation error you'll
realize that a problem can only occur when and if you violate a
constraint by generating a duplicate value. So in fact you can do
exactly what you have described.
I notice this is slightly clearer in 2005 BOL:
"If the value of new_reseed_value is less than the maximum value in the
identity column and a PRIMARY KEY or UNIQUE constraint exists on the
identity column, error message 2627 will be generated on subsequent
references to the table."

> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
woefully and needlessly misused and abused by people who don't even
realize what they are doing wrong.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||of course you can't do that.
why are you adding a row with this very high value? is it some kind of
stub row? why not make it 0 or go negative with it instead?
ktrock wrote:
> Hello. I see that we're not permitted to reseed a incremental column to a
> value that's lower than the highest value in the table. I guess that's in
> there for our protection but is there any way around this? On an integer
> column I manually set row to a value of 2 billion and had planned to resee
d
> to a very low number never to approach it. If we have to start at 2 billio
n
> it's possible that we'll run out of room.
> Guess I can change the column to a large int. Is anyone thinking that
> identity fields are more hassle than they're worth?
> TIA,
> Ken Trock|||if you really want to do that, move all the rows with idenities higher
than the value to another table, set the seed to the value, and move
the rows back with IDENTITY_INSERT on|||holy crap - i always thought this was a silly enough thing to be want to
do, but to actually be able to do it...
Ken - ok - i was wrong, you can do it. but i still say you shouldn't
(and shouldn't be able to...)
David Portas wrote:
> ktrock wrote:
>
>
> I think you are referring to this comment in Books Online:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
> http://msdn.microsoft.com/library/d...asp?frame=true
> This implies that setting a value lower than the maximum won't work but
> if you know that error message 2627 is a key violation error you'll
> realize that a problem can only occur when and if you violate a
> constraint by generating a duplicate value. So in fact you can do
> exactly what you have described.
> I notice this is slightly clearer in 2005 BOL:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column and a PRIMARY KEY or UNIQUE constraint exists on the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
>
>
>
> Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
> woefully and needlessly misused and abused by people who don't even
> realize what they are doing wrong.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks all for the replies. Will try 1 of these options.
Ken
"David Portas" wrote:

> ktrock wrote:
> I think you are referring to this comment in Books Online:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
> http://msdn.microsoft.com/library/d...asp?frame=true
> This implies that setting a value lower than the maximum won't work but
> if you know that error message 2627 is a key violation error you'll
> realize that a problem can only occur when and if you violate a
> constraint by generating a duplicate value. So in fact you can do
> exactly what you have described.
> I notice this is slightly clearer in 2005 BOL:
> "If the value of new_reseed_value is less than the maximum value in the
> identity column and a PRIMARY KEY or UNIQUE constraint exists on the
> identity column, error message 2627 will be generated on subsequent
> references to the table."
>
> Yes they are a gerat big hassle. A lot of the time IDENTITY columns are
> woefully and needlessly misused and abused by people who don't even
> realize what they are doing wrong.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql