We help IT Professionals succeed at work.
Get Started

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

Dale Fye
Dale Fye asked
on
55 Views
Last Modified: 2020-03-21
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
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE