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

 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!  

 Hi...

Can you explain what exactly you mean by relational algebra???

 Yeah sure.

Instead of:

Select T1.*

from Table1 T1 inner join Table 2 T2 on T1.Col1 = T2.Col1

Rather use:

Select T1.*

from Table1 T1, Table2 T2

where T1.Col1 = T2.Col1 

Tjaard:

 Yeah sure.

Instead of:

Select T1.*

from Table1 T1 inner join Table 2 T2 on T1.Col1 = T2.Col1

Rather use:

Select T1.*

from Table1 T1, Table2 T2

where T1.Col1 = T2.Col1 

Is it?

Can you execute with INNSER JOIN again and see if you get error?

Also look at the execution plan of both the queries

I've checked the execution plans of both those queries, and both result in a Hash Match/Inner Join being performed. Are you sure this what caused the error?