We help IT Professionals succeed at work.

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

Dale Fye
Dale Fye asked
on
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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 FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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.
CERTIFIED EXPERT

Commented:
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.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Thanks for the help, but I've done:
DBCC CHECKTABLE ('tbl_Cygnet_Readings_Frequent')
dbcc shrinkfile (..._log, 10)
cleared all indexes and recreated them

none of that resolve the problem, so I resorted to restoring from a backup.  my concern is that this may be a data issue and I may run into it again as I reload the production data into this reporting database