Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Unanswered: A severe error occurred on the current command.

    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

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds like a server-specific issue. Is the offending server different from the ones where the procedure runs successfully?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2008
    Posts
    6
    Only difference as far as I can see is that it's 64 bit, and not 32 bit SQL2005 installation.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So which one the query fails on? 64-bit?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2008
    Posts
    6
    yes, it fails on the 64bit. But, as stated earlier, I believe the issue is not the 64bit machine but rather the cache memory available for queries in SQL.

    Is there any way to check and/or increase the cache memory available for queries in SQL?

  6. #6
    Join Date
    Jun 2008
    Posts
    6
    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!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Tjaard
    Moral of the story....Inner join BAD, Relational Algebra GOOD!
    Novel thought that.

    Inner joins are part of relational algebra, and a significant part too. Like breathing is part of normal bodily functions. Applying that to your original statement yields "Breathing bad, normal bodily functions GOOD!" which is funny to think about, but it doesn't make much sense to me.

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Tjaard, are you saying that the amount of data involved in the query across all servers is the same, and only 64-bit machine was failing? I am hoping your answer will be NO, but if it is YES, - then...forget about it, I am glad you resolved it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Oct 2005
    Posts
    1

    Arrow This might b the issue


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •