rollback trans error identity column

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

SQL Server 2000 Forums

SQL Server Development (2000)

rollback trans error identity column

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2006-04-26 : 20:47:27

Code is posted below:

I check to make sure both stored procs are correct. If one of them gives error then I roll back the entire thing.

It works (it won't execute both sp's), but the identity column is wrong. If I make it so I get an error on my asp page when it calls one of the stored procedures, then refresh like 5 times. Then fix the error and rerun the asp page, the db table identity field will skip 5 numbers.

So if after 3 I get an error and refresh the page 5 times, then the db table looks like this:

1
2
3
8

It should be:
1
2
3
4

Here is my asp code

con.BeginTrans

set cmd = server.createobject("adodb.command")
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Login"
cmd.Parameters.Append cmd.CreateParameter("@IP_Address", adVarChar, adParamInput, 15, "1.1.1.1")
cmd.Parameters.Append cmd.CreateParameter("@UserId", adChar, adParamInput, 15, user)

cmd.execute
set cmd = nothing

set cmd = server.createobject("adodb.command")
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "LoginTemp"
cmd.Parameters.Append cmd.CreateParameter("@IP_Address", adVarChar, adParamInput, 15, "1.1.1.1")
cmd.Parameters.Append cmd.CreateParameter("@UserId", adChar, adParamInput, 15, user)

cmd.execute
set cmd = nothing

if con.errors.count=0 then
con.CommitTrans
else
con.RollbackTrans
end if

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 20:59:58

There is no error in the IDENTITY column. That is the way an IDENTITY column is supposed to work.

If you insert 5 rows, and then rollback the transaction, SQL Server does not reuse the numbers that were rolled back.

CODO ERGO SUM

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-04-26 : 21:34:13

And....just a thought, your database and application shouldn't be setup to care if there are spaces in the identity column. This is especially true if you are using it as a "primary key" like many people do.

You can use DBCC CHECKIDENT to reset identity columns to a specified number. It takes elevated priviliges though, and should be avoided like the plague.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

abarsami
Yak Posting Veteran

68 Posts

Posted - 2006-04-26 : 22:52:24

Thank you so much. I spent one full day on this thinking it was an error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources