August 28, 2008 at 1:29 am
Hia guys, hope all is well.
I've run into this problem only once, and sadly that was on a major client's server.
Basically I'm running a huge Stored Procedure that places data into about 15 temp tables before binding it all together into a single result set.
My problem is it seems that SQL runs out of memory or something halfway through the proc and I get the infamous :
"Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."
error.
I'm using alot of temp tables, but there's only a total of about 20 records in them when the error occurs.
To give you some background on the situation, I need to retrieve documents from a multitude of tables. do calculations on them and then match them up (group them together) with Invoices. i.e. Invoices, Credit Notes, Payments, reconcilliations, Journal ertires etc are retrieved, the final result needs to show which document movements took place relevant to a specific Invoice.
The temp tables are required because of the calculations that needs to done on the documents.
Now, the querie runs 100% on all but one server I've tested on. (4 out of 5)
This error even occurs when I'm not using the stored proc, but rather running the script directly.
If anyone has any ideas I would truly appreciate your input.
Thanks, Tjaard
August 29, 2008 at 1:41 am
I have found the cause of the error. The problem was that more memory was being used that what was available in the procedure cache of SQL. The reason why the querie was so memory intensive was because of a multitude of inner joins. After replacing all the inner joins with relational algebra was not only the error resolved, but performance was greatly enhanced.
Moral of the story....Inner join BAD, Relational Algebra GOOD! 😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply