- Query Syntax
- SQL
- Microsoft SQL Server
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
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:
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
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
SELECT TOP 5 * from tbl_Cygnet_Readings_Frequent
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
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)
SELECT FacilityID, COUNT(*)
from tbl_Cygnet_Readings_Frequent
WHERE LastModified >= '2020-03-01'
GROUP BY FacilityID
SELECT FacilityID, MAX(LastModified)
FROM tbl_Cygnet_Readings_Frequent
group by FacilityID
First of all you should check the table by
DBCC CHECKTABLE ('tbl_Cygnet_Readings_Freq
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.
DBCC CHECKTABLE ('tbl_Cygnet_Readings_Freq
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.
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.
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.
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.
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.
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
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
Explore More Content
-
![]()
SolutionQuery of linked SQL Server table from Access returns error (-7711) and SSMS returns "a severe error occurred on the current command" -
![]()
ArticleHow to Attach SQL Database Which is Not Properly Detached -
![]()
VideoQuery Current Session Information via SQL -
![]()
Cloud Class®SQL 2014 Developer P3: Partitions, Queries and XML
Premium Content
You need an Expert Office subscription to comment.Start Free Trial