May 9, 2010 at 9:45 pm
Hi everyone.
We have a table with about 3 million records and probably 40 fields in it.
It has worked fine for about 5 years (and we have archived to reduce the number of records, it is no larger than previously either), until last Friday when users started reporting sporadic problems from the Access 2003 client interface.
It took us awhile to work out that all errors being reported were on items being queried from this particular table.
We ran some tests from the SQL Management Console in a query window and sure enough found a problem.
Trying to run the following Select Statement illicits the following results, in every case:
SELECT tblCD.* FROM tblCD
ORDER BY tblCD.Call_Ref_No DESC;
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I note for the record that it doesn't matter which of the 40 or so columns one tries to sort by or whether sorting ascending or descending... in other words it doesn't seem to be a specific index causing the problem.
Nor does it matter if you don't query all the columns, in other words, the following also produces the same error.
SELECT tblCD.Call_Ref_No, tblCD.Tech
FROM tblCD
ORDER BY tblCD.Call_Ref_No;
The error is pretty consistent.
Oddly, users using a particular screen which interacts with this table quite a lot are not having problems adding/editing or updating through the Access interface, but some reports and other queries from the access client fail with a -7711 reserved error, which roughly translates to the same Severe Error on the SQL Server that I've shown above.
Anyone got any ideas?
I've run a dbcc CheckDB which returned no errors, among other various things, to no avail.
Help!
May 9, 2010 at 10:21 pm
Yes... I've seen such a thing just slam a 4 CPU system into the wall on UPDATES. I believe the problem may be that you're using the table name everywhere instead of a table alias. Note that Microsoft has deprecated the ability to write such code. Instead, it should be written as follows...
SELECT cd.Call_Ref_No, cd.Tech
FROM dbo.tblCD AS cd
ORDER BY cd.Call_Ref_No;
Notice that I also used the proper two part naming convention for the table in the FROM clause, as well.
Either that or you really need to do some major index maintenance.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2010 at 11:01 pm
Um, no, in SQL2005 it doesn't care whether you use an alias or not, makes no difference and certainly doesn't make Order By clauses fail.
Keep in mind this is also failing from attached ODBC links in an Access front-end, and has been running just fine for about 5 years until last Friday.
It runs fine in a backup, read-only copy we have for reporting as well as two copies that have been restored to test servers, which probably means that whatever the problem is, is being effectively compacted and repaired during the restore process.
My feeling is that it is some sort of corrpution in the table or an indexing issue, however I have also run a Reorganize on the table, to no avail, and as I mentioned earlier, dbcc CheckDB didn't turn up any corruptions at all in the database.
Only other thing I can come up with is that it's a network issue, but it seems too consistent to be a network issue only. Maybe a disk is going, hard to say. Perhaps one of our RAIDs is playing up?
Anyone else got any other ideas?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply