SQL Server Performance

A severe error occurred on the current command.

Discussion in 'SQL Server 2005 General Developer Questions' started by Tjaard, Aug 28, 2008.

  1. Tjaard New Member

    Hia guys, hope all is well.

    I've run into this problem only once, and sadly that was on a major client's server.

    BasicallyI'm running a huge Stored Procedure that places data into about 15 temptables 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.

    Togive you some background on the situation, I need to retrieve documentsfrom a multitude of tables. do calculations on them and then match themup (group them together) with Invoices. i.e. Invoices, Credit Notes,Payments, reconcilliations, Journal ertires etc are retrieved, thefinal result needs to show which document movements took place relevantto 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
  2. Tjaard New Member

    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! [:D]
  3. rohit2900 Member

    Hi...
    Can you explain what exactly you mean by relational algebra???
  4. Tjaard New Member

    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
  5. Madhivanan Moderator

    [quote user="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
    [/quote]
    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
  6. martins New Member

    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?

Share This Page