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.
| Author |
Topic |
|
vbwizard
Starting Member
12 Posts |
 Posted - 2008-01-18 : 12:13:42
|
| Hi all,I found problem with my database and was wondering if anyone here could shed some light on the issue.I have two tables, Absences and AbsenceDates. The first one records the absence of an employee and the second one records a record for each day of the occurance. I do a full select on the second table and I see primary keys that do NOT exist in the select of the second table. so I dug further and here is what I found.Select * from Absences (rowcount in Query Analyser is: 20883)Select * from Absences Order By AbsenceID Desc (rowcount is 443)The second select contains the data that I am missing in the first select. So, I called a friend and they said to run DBCC CHECKDB and I did. The data came back as follows... DBCC results for 'Absences '.There are 21337 rows in 243 pages for object 'Absences'.CHECKDB found 0 allocation errors and 0 consistency errors in database 'EmployeeAbsenteeism'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.now if you add up the rows that the other two selects return it comes to 21326, not 21337. I am assuming that the value that DBCC gets is from sysobjects and that some sort of update would need to be run for it be accurate. This I don't care about too much, what I really need is for my main select statement to return ALL of the data, not just what it feels like returning.My experience is with programming mainly (6 years in .net) and not DBA, so any help would be greatly appreciated.Cheers,BrentSorry, this was supposed to go to data corruption forum... reposting..You should check out www.hogwarts.tv ... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-18 : 12:22:04
|
| Are the AbsenceID of records returned by both queries of somewhat continuos values? |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-18 : 12:35:13
|
| am slightly confused..."I do a full select on the second table and I see primary keys that do NOT exist in the select of the second table"...did you mean to use the word 'first' in here somewhere instead of "'second' twice?....because it doesn't make sense otherwise.there's no possibility ofa) being in the wrong databaseb) using multiple database owners (aka schema's?)c) uncommitted records from an open transaction?select a.* from table1 aleft join table2 b on a.commonfield = b.commonfieldwhere b.commonfield is nullthis will list records in #1 but not in #2reverse the table names (but nothing else) to get the reverse. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-18 : 12:35:59
|
| It also suggests that you need some FK Contraints applied to table #2...to prevent orphan records!! |
|
|
|
|
|
|
The mystery of the missing data.....