views:

95

answers:

2

I have a SQL Server database. One of the tables has a column that, for lack of a better word, is acting corrupted. I can select * or even select that specific column and it works fine. I can perform functions like count, order by and group by on all other columns with no problems. But if I try and do one of those functions on this one column, it errors out with the following message: Error: (State:S1000, Native Code: E28)

Also, I am connected to the database using winsql.

Has anyone ever seen this kind of behavior before?

Additional info:

select colname from tablename

works fine

select colname from tablename order by colname

produces the error

+2  A: 

Try rebuilding indexes. I had similar problem and rebuilding indexes fixed it. In another case, rebuilding indexes did not work, so I had to restore the nightly backup. Thank goodness we had nightly backups for the development database.

AMissico
+1  A: 

If the SQL database is reporting corruption / this really is corruption - start being very careful. The corruption might start manifesting itself with 823 / 824 and other high severity errors. Keep your cool and run DBCC CheckDB - find out the extent of the corruption. Don't detach it or start performing random actions.

You should take a tail of log backup of the transaction log before you proceed - you may well need it if you are forced to restore. I would probably take this with a COPY_ONLY option.

CheckDB might take some time, but let it finish and see how many corruptions are being reported, and on what objects they are being reported.

Corruptions on NC indexes can be solved easily through an index rebuild, corruptions with torn pages are going to be harder.

If CheckDB refuses to finish and error's itself, you have a real problem and will be exporting what data you can / restoring from a backup.

One of the best places to read up on corruptions and finding / fixing / when it's game over is Paul S. Randal's blog. http://www.sqlskills.com/blogs/paul/

This applies only if you really are looking at corruption instead of a piece of dodgy SQL.

Andrew
Does " select * from tablename order by columnname" count as dodgy sql?
Kevin
It shouldn't be, but run it via SQL mgmt studio to make sure that nothing is interfering with it prior to be executed at the server. If that fails, then get a note of the error message and start acting carefully.
Andrew