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
 General SQL Server Forums
 Data Corruption Issues
 How can I tell if my DB is corrupt?

Author  Topic 

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 15:57:51
Hi everyone!

I have a table where 14 rows display out of sync with the other 1500 rows in the table for some reason. I have thouroughly checked the data in those rows and I can't see any reason for those rows displaying out of sync. I am new fairly new to SQL but am very confident that my queries are not at fault.

However, I have run DBCC CHECKDB on the database and no errors came back. Does the fact that no errors were reported mean it's definately not corrupt? Is it worth running a repair anyway (I have never done a repair before so don't want to do one unless I have to)?

Many Thanks

Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-12 : 16:45:15
What do you mean "out of sync"?
Wrong values? Not expected sequence?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 16:57:41
Hi Peso

I haven't forgot to get back to you about the other question you answered for me (for which you were right and I apologise...but will answer that post seperately ASAP).

Anyway, when I do a simply select * from tableX order by column. All 1500 records return in the correct order apart from just 14 records. I am able to update these records perfectly fine and they "function" OK. The rest of the database is also perfectly fine. It's just really strange how these particular 14 records return in the result out of sync. The 14 records concerned are all next to each other in the table ie, records 200 to 214 and they all return out of sync but in a block in the result. It's almost like these records are not affected by the ORDER by clause for some reason.

Have you ever seen anything like this before?

Cheers

Lee
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-12 : 17:00:07
How did the records appear to be out of synch?
Do you have a screen shot? Were there "hidden" characters? Ascii 160, for example?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 17:25:42
OK, I guess it must be my inexperience again! I have managed to get a correct result but still not sure why my original query causes the issue it does. I'm not sure if I can upload screen shots to this forum or even know how to??

Anyway, the query that had the abnormality:

SELECT ParentPath, Category, ID
FROM tbl_dir_categories
WHERE PID IS NOT NULL
ORDER BY ParentPath

This query works:

SELECT ParentPath, ID
FROM tbl_dir_categories
WHERE PID IS NOT NULL
ORDER BY ParentPath

Note that removing the Category from select allowed the 14 'odd' records to fall into line. Obviously that's not the right thing to do?? Just not sure why out of 1500 records, only 14 were out of sync. Does the order of selection of columns have any influence over sort order or anything? I didn't think it did.

Thanks

Lee
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 17:34:46
Hi Peso

Further to my last post, here is a sample output. It's the Computer Services categories....buy hey! Cutting and pasting into here..it looks likt there is a space there....hold on!

Computers and Internet > Computer Services > Information Technology
Computers and Internet > Computer Services > IT Consultancy
Computers and Internet > Computer Services > IT Disposal
Computers and Internet > Computer Services > IT Recycling
Computers and Internet > Computer Services > IT Support Services
Computers and Internet > Computer Services > Laptop Repair
Computers and Internet > Computer Services > Server Maintenance
Computers and Internet > Computer Services > Server Monitoring
Computers and Internet > Computer Consumables
Computers and Internet > Computer Consumables > CD-DVD Duplication Services
Computers and Internet > Computer Consumables > CD-DVD Media and Packaging
Computers and Internet > Computer Consumables > CD-DVD Printers
Computers and Internet > Computer Consumables > Printer Supplies
Computers and Internet > Computer Hardware
Computers and Internet > Computer Hardware > Computer Printers
Computers and Internet > Computer Hardware > Network Hardware
Computers and Internet > Computer Hardware > PDA
Computers and Internet > Computer Services
Computers and Internet > Computer Services > Business Systems
Computers and Internet > Computer Services > Games Console Repairs
Computers and Internet > Computer Services > Printer Repair
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-12 : 17:37:02
quote:
Originally posted by LeeNewman

Computers and Internet >  Computer Services > Information Technology
Computers and Internet >  Computer Services > IT Consultancy
Computers and Internet >  Computer Services > IT Disposal
Computers and Internet >  Computer Services > IT Recycling
Computers and Internet >  Computer Services > IT Support Services
Computers and Internet >  Computer Services > Laptop Repair
Computers and Internet >  Computer Services > Server Maintenance
Computers and Internet >  Computer Services > Server Monitoring
Computers and Internet > Computer Consumables
Computers and Internet > Computer Consumables > CD-DVD Duplication Services
Computers and Internet > Computer Consumables > CD-DVD Media and Packaging
Computers and Internet > Computer Consumables > CD-DVD Printers
Computers and Internet > Computer Consumables > Printer Supplies
Computers and Internet > Computer Hardware
Computers and Internet > Computer Hardware > Computer Printers
Computers and Internet > Computer Hardware > Network Hardware
Computers and Internet > Computer Hardware > PDA
Computers and Internet > Computer Services
Computers and Internet > Computer Services > Business Systems
Computers and Internet > Computer Services > Games Console Repairs
Computers and Internet > Computer Services > Printer Repair
Yes it is a space.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 17:38:52
When I cutted and pasted the sample into the post, there were spaces before the computer services category ....i'm sure there was. But as you can see there are not. I think i am going mad!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-12 : 17:40:46
This forum appearantly does not show multiple spaces.
Including the text in tags [ code ] and [ /code ] <- without spaces
will display original text.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 17:44:18
YEEAAAAHHHHHH!

Records updated and results coming back fine now.

So all the problem was......was my eyesight

Thanks Peso! I guess i'll leave a database repair for another day
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-03-12 : 17:45:42
All your problem was bad data (leading space for 14 records.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

LeeNewman
Starting Member

16 Posts
Posted - 2009-03-12 : 17:53:14
Come to think of it...it's still puzzling how the category column which was not affected by 'bad data' affected the results? The spaces were in the ParentPath column. When I excluded the Category from the SELECT the ParentPath returned in sync even with the spaces.

Anyway, I'm not too worried as my actual problem is now solved. I just find it weird.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site