SQL - 5 records that cannot be referenced by inner join

Question

4.00/5 (1 vote)

See more:

Hello.

I am using SQL 2005 Express.

I have a table with an auto increment field named rec_id. This table has 5 'funny' records, rec_id 33941 to 33945. Fyi, the table has no problem adding records and the rec_id now has gone beyond that 5 records, 34000+. We only discovered these 5 records today.

If I do a "Select rec_id from masterreceipt", that set of 5 records will always remain at the bottom 5, even if I added "order by rec_id" to the command. The result will look something like this.
.
.
.
34810
34811
34812
33941
33942
33943
33944
33945

But if I further add "desc" to the same command, that set of 5 records will not appear at all.

And also, that 5 records cannot be 'inner join' by other tables. It will not return the rows.

I am pretty sure that set of records has never been thru an update or a delete command. I've checked the content as well, nothing out of the ordinary.

Can anybody please tell me what is going on?

Thank you.

Posted 4-Oct-12 1:45am

Zakirom

Add a Solution

Comments

Kuthuparakkal 4-Oct-12 8:41am

stop sql server service and then start to see if any problem

Zakirom 4-Oct-12 8:47am

Tried but no effect.

Also, that 5 records cannot be deleted or updated

Zakirom 4-Oct-12 8:48am

Thank you btw.

Kuthuparakkal 4-Oct-12 8:51am

try : detach and attach your db
if not working, take back up, reinstall sql, and restore your db

Zakirom 4-Oct-12 9:00am

Also tried, no effect. I've also backup the db and restore it at another server. Same problem persist.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zakirom · Accepted Answer · 2012-10-04T05:02:00

I modified the table, turning off the IsIdentity property of the said field. When I checked, the 5 records were back in place, at the order of entry. After turning back on the IsIdentity property, everything works fine again.

But I wonder what causes it. I need to explain it to my customers coz this seemingly simple problem caused a customer service failure resulting in a complain. I need to tell them that it is not my program that causes it.