Avatar of Dale Fye
Dale Fye
Flag for United States of America asked on

Error: "A severe error occurred on the current command" when attempting query which uses any form of sorting

I'm working with a clients on-site SQL Server and an Access front-end

Recently I started getting an error in Access when I attempted to write to one of the tables in the database.  I was unable to resolve this issue from within Access so I turned to SSMS and now when I run this query
SELECT TOP 1000 *
from tbl_Cygnet_Readings_Frequent 
order by report_DT

Open in new window

I get the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Open in new window

I'm able to run:
SELECT TOP 5 * from tbl_Cygnet_Readings_Frequent 

Open in new window

and get these results.
ID     FacilityID	Report_DT                 PDIFF      PSTATIC    RGAS       LastModified               SSMA_Timestamp        TMPGAS
3198031   10521	2018-04-20 20:49:00.097   40.61012   817.1606   1917.925   2018-04-20 20:49:00.097    0x00000000003902E6    74.510755
3197797   10521	2018-04-20 21:30:05.473   42.62617   818.8284   1965.338   2018-04-20 21:30:05.473    0x00000000003901E2    75.17493
3197673   10521	2018-04-20 21:49:35.800   42.16301   819.0925   1953.901   2018-04-20 21:49:35.800    0x0000000000390165    75.59758
3197415   10521	2018-04-20 22:30:05.313   41.17471   819.7466   1930.659   2018-04-20 22:30:05.313    0x000000000039001B    76.0209
3197222   10521	2018-04-20 22:50:40.303   41.18724   820.0338   1932.077   2018-04-20 22:50:40.303    0x000000000038FF4E    75.708015

Open in new window

But when I add an Order By clause containing any field other than FacilityID (nvarchar -20), I get the error mentioned above.

I can filter the data (over 9M records) using a where clause on any of the fields, and can even generate grouped data with queries like:

SELECT cast(Pstatic as integer) as PD2, COUNT(*)
from tbl_Cygnet_Readings_Frequent
WHERE LastModified >= '2020-03-01'
GROUP BY CAST(pstatic as integer)

Open in new window

or
SELECT FacilityID, COUNT(*)
from tbl_Cygnet_Readings_Frequent
WHERE LastModified >= '2020-03-01'
GROUP BY FacilityID

Open in new window

but as soon as I try something that requires a sort, it gives me the same error:
SELECT FacilityID, MAX(LastModified)
FROM tbl_Cygnet_Readings_Frequent
group by FacilityID

Open in new window

Microsoft SQL Server

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Pavel Celba

First of all you should check the table by
DBCC CHECKTABLE ('tbl_Cygnet_Readings_Frequent')

More info: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checktable-transact-sql?view=sql-server-ver15

You should also check the whole database by DBCC CHECKDB.
And you should also recreate all indexes.

Additional steps would be to create a copy of the table and test your queries on the new table.

Then you could tell what SQL Server version and SP or CU are we talking about. Some versions need to apply service packs to fix this error.

If everything fails you have to contact Microsoft and provide them the SQLDump file.
Dale Fye

ASKER
This started about a 10 days ago, and other emergencies prohibited me from doing much with it until now.
The good news is that I have a full backup from several days before that which is not generating this error, so I'll restore the database from the full and regenerate the numbers.
Pavel Celba

If the table was not updated after the backup then restore is OK.
If you are OK with data loss then the restore is also OK.

You should investigate whether the issue is in the table itself or in its indexes. If just indexes are corrupted then you may simply recreate them w/o data loss. Of course, clustered index corruption means data corruption obviously.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.