|
|
You're in the
sql server programming
group:Missing records in database...
sql server programming:
When I look at the auto-incrementing IDs there is a missing ID here and there. There is nothing in my software that can delete a record from this table so it must have never been stored to begin with, but it is using up an ID. There is a 2nd table that gets written to also when this one gets written to and that one isn't missing any records. It's not giving any errors so I don't know what's going on. I write to the 2nd table first, then do some stuff in my code, then I begin a transaction, write to the 1st table (as well as a couple others), then commit the transaction. Does anyone know what could cause this or how I can diagnose where the problem may be occurring? Thanks!
Do you have trigger/s on the table/s? Can you post entire code to reproduce
the problem along with a version of sql server and service pack installed on? [quoted text, click to view] <hurricane_number_one@yahoo.com> wrote in message news:1163902527.531682.31460@h54g2000cwb.googlegroups.com... > One table in my database keeps dropping or never storing some records. > When I look at the auto-incrementing IDs there is a missing ID here and > there. There is nothing in my software that can delete a record from > this table so it must have never been stored to begin with, but it is > using up an ID. There is a 2nd table that gets written to also when > this one gets written to and that one isn't missing any records. It's > not giving any errors so I don't know what's going on. > > I write to the 2nd table first, then do some stuff in my code, then I > begin a transaction, write to the 1st table (as well as a couple > others), then commit the transaction. Does anyone know what could > cause this or how I can diagnose where the problem may be occurring? > > Thanks! >
If you rollback, the identity value has been consumed. Do not use identity if you for some reason
require consecutive numbering. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] <hurricane_number_one@yahoo.com> wrote in message
news:1163902527.531682.31460@h54g2000cwb.googlegroups.com... > One table in my database keeps dropping or never storing some records. > When I look at the auto-incrementing IDs there is a missing ID here and > there. There is nothing in my software that can delete a record from > this table so it must have never been stored to begin with, but it is > using up an ID. There is a 2nd table that gets written to also when > this one gets written to and that one isn't missing any records. It's > not giving any errors so I don't know what's going on. > > I write to the 2nd table first, then do some stuff in my code, then I > begin a transaction, write to the 1st table (as well as a couple > others), then commit the transaction. Does anyone know what could > cause this or how I can diagnose where the problem may be occurring? > > Thanks! >
I don't use triggers. I can't reproduce the problem, I just see it
happening at clients by looking in their database. I'm using Recordsets in VB to write the data. Is it possible for the data to not get stored like this without throwing an error or is there something else I should look for? [quoted text, click to view] Uri Dimant wrote:
> Do you have trigger/s on the table/s? Can you post entire code to reproduce > the problem along with a version of sql server and service pack installed > on? > > > > > > > <hurricane_number_one@yahoo.com> wrote in message > news:1163902527.531682.31460@h54g2000cwb.googlegroups.com... > > One table in my database keeps dropping or never storing some records. > > When I look at the auto-incrementing IDs there is a missing ID here and > > there. There is nothing in my software that can delete a record from > > this table so it must have never been stored to begin with, but it is > > using up an ID. There is a 2nd table that gets written to also when > > this one gets written to and that one isn't missing any records. It's > > not giving any errors so I don't know what's going on. > > > > I write to the 2nd table first, then do some stuff in my code, then I > > begin a transaction, write to the 1st table (as well as a couple > > others), then commit the transaction. Does anyone know what could > > cause this or how I can diagnose where the problem may be occurring? > > > > Thanks! > >
[quoted text, click to view]
> One table in my database keeps dropping or never storing some records. > When I look at the auto-incrementing IDs there is a missing ID here and > there. That doesn't necessarily mean a row is missing. http://databases.aspfaq.com/database/why-are-there-gaps-in-my-identity/autoincrement-column.html
I know they are missing because every time a row is written in this
table a receipt is printed out and there are receipts that don't have corresponding rows in the database and if I look at the timestamp on the receipt, the place where it would be located in the database has a missing ID in the sequence. [quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> > One table in my database keeps dropping or never storing some records. > > When I look at the auto-incrementing IDs there is a missing ID here and > > there. > > That doesn't necessarily mean a row is missing. > http://databases.aspfaq.com/database/why-are-there-gaps-in-my-identity/autoincrement-column.html
[quoted text, click to view]
hurricane_number_one@yahoo.com wrote: > I know they are missing because every time a row is written in this > table a receipt is printed out and there are receipts that don't have > corresponding rows in the database and if I look at the timestamp on > the receipt, the place where it would be located in the database has a > missing ID in the sequence. > Maybe the INSERT itself is failing, due to a constraint violation for example. Are you properly capturing any errors in your insert procs? The other possibility is that some other process deletes rows without you realising. It is far more likely that the rows never committed or were deleted than that the data is "corrupt" in some way. One way to track this down is to use a Profiler trace to capture any SQL statements against your table. -- 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 --
I have made sure that all errors are properly logged. I also added
some code which logs every step and the logs show that it is starting the transaction, updating/inserting the recordset then comitting the transactions all fine without any errors but I'm still getting missing rows now and then. This is only happenning when I'm doing a transaction that involves updating many tables. [quoted text, click to view] David Portas wrote:
> hurricane_number_one@yahoo.com wrote: > > I know they are missing because every time a row is written in this > > table a receipt is printed out and there are receipts that don't have > > corresponding rows in the database and if I look at the timestamp on > > the receipt, the place where it would be located in the database has a > > missing ID in the sequence. > > > > Maybe the INSERT itself is failing, due to a constraint violation for > example. Are you properly capturing any errors in your insert procs? > The other possibility is that some other process deletes rows without > you realising. It is far more likely that the rows never committed or > were deleted than that the data is "corrupt" in some way. One way to > track this down is to use a Profiler trace to capture any SQL > statements against your table. > > -- > 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 > --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |