SQL Server Performance

Internal SQL Server error... Any ideas?

Discussion in 'General Developer Questions' started by Negative, Sep 18, 2003.

  1. Negative New Member

    I have a query that I have been running on a regular basis for several weeks now, and just tonight I got a page that the process that calls it had failed. I have narrowed down the problem to 1 specific part of the query, however I am about at my wits end trying to figure out the cause. Any help would be appreciated.

    Here is the offending query:

    SELECT *
    FROM (
    SELECT a.Field1, a.Field2, b.Field2 AS Field3
    FROM ViewOfLiveData a
    LEFT OUTER JOIN HistoryDataTable b
    ON (a.Field3 = b.Field2
    AND a.Field1 = b.Field1)
    ) AS c
    INNER JOIN ViewOfLiveData d
    ON (d.Field1 = c.Field1
    AND d.Field2 = c.Field2)

    And this is the error it returns:

    Server: Msg 8624, Level 16, State 13, Line 1
    Internal SQL Server error.

    The subquery runs fine by itself. The error only occurs when I join it back to the view, and if I join to another table it works fine. As I mentioned earlier, the query has been running fine for several weeks, and we have been on sp3 this whole time. The only thing I know of that has been changed is that the latest security patch for that new worm that is going around was applied earlier today. I also checked our performance monitor trends and everything seems to be normal (i.e. no major fluctuations in anything recently).

    Anyone have any ideas?
  2. bambola New Member

  3. Negative New Member

    Thanks for the links bambola. I searched MSDN already, and didn't find anything that really pertained to my problem. The answers from dbforums.com were equally helpfull. I ended up working around the problem by rewriting the query, however it really irks me that this worked for several weeks then decides to quit for no apparent at 12:30 AM.
  4. gaurav_bindlish New Member

    Is there any maintainence job going on at this period?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. satya Moderator

  6. Negative New Member

    There was a maintenance job running at the first time I ran into the problem, however the query still did not work after the job completed. I tested the query several times throughout the day, and on several different servers. Nothing seems to fix the problem, the query does not run anywhere. I think I am going to chalk this one up as a bug with the patch we applied, and since I already rewrote the query it really isn't causing me any problems anymore.<br /><br />I guess I found a good problem for you guys eh? Three moderators trying to answer my question... now if we just got Brad to give some input <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  7. Twan New Member

    Hey Negatve...

    out of interest...

    what is the definition of the view that you are using?
    also what did you change the query to?
    is field1, field2 the PK for ViewOfLiveData?

    It does seem odd that the query just stopped working without any obvious cause...

    Cheers
    Twan
  8. gaurav_bindlish New Member

    What happens if you write the query like this..
    SELECT a.*, b.Field2 AS Field3
    FROM ViewOfLiveData a
    LEFT OUTER JOIN HistoryDataTable b
    ON (a.Field3 = b.Field2
    AND a.Field1 = b.Field1)
    I don't see any need for joining with the view ViewOfLiveData in the query again as you are joining the view with itself which will not filter any results for you.

    Just a comment... Please let me know if I am doing wrong.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. Negative New Member

    The view is a fairly large query joining 8 tables together with a lot of aggregates and other functions used to manipulate the data, unioned to another large query joining 12 tables with more manipulation of the result set.<br /><br />I guess I should have specified this before, but this query is used in an insert statement, so I wanted to filter on the inside query where certain rows did not match up and on other criteria. I changed the query to remove the join to the derived table, so that the inner query selects all the fields I need in the final result set. Then I used the inner query in a derived table where I selected the fields from the derived table which I need for the insert, and used the where clause just as before. I probably should have written it this way in the first place, but I was spacing out as usual <img src='/community/emoticons/emotion-1.gif' alt=':)' /> The final result looks something like this:<br /><br />INSERT INTO DestinationTable (&lt;field list&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />SELECT &lt;field list&gt;<br />FROM (<br />SELECT a.&lt;field list&gt;, b.Field2 AS Field3<br />FROM ViewOfLiveData a<br />LEFT OUTER JOIN HistoryDataTable b<br />ON (a.Field3 = b.Field2<br />AND a.Field1 = b.Field1)<br />) AS c<br />WHERE &lt;filter criteria&gt;

Share This Page