?
Solved

Internal SQL Server Error - Subquery and join

Posted on 2002-05-14
10
Medium Priority
?
441 Views
Last Modified: 2012-06-27
Hi - I know this is a bit of big question but I am really mystified.

I have a query which should be based on a view (I have put the view's query inside for this example) which needs to have the result put into a variable. Running this example causes error 8624 - 'Internal SQL Server Error'. It does not cause an error when: you remove the inner join to the view; OR you just run the query without passing it into a variable; OR you remove the WHERE clause with the subqueries.

Here is the example:

declare @var int

select @var = (

select count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
     r2.[sequence] =
     CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                     WHERE      r3.request_id = r1.request_id AND
                         r3.event_type = 'RELEAS' AND
                          r3.[sequence] > r1.[sequence]
                     )
          THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                 WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
                )
          ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                 WHERE  r5.request_id = r1.request_id AND
                     r5.event_type = 'RELEAS' AND
                     r5.[sequence] > r1.[sequence]
                )
          END
--***** end of view
) re
inner join request r on re.request_id = r.request_id
)

select @var
0
Comment
Question by:griffinj01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 2

Expert Comment

by:BobSnyder
ID: 7008526
Two suggestions
Use sp_configure to check the query governor cost limit, it should be 0 to remove limits.
If any join column is NULLABLE, try converting its values to non null like
ISNULL(r3.event_type, '') = 'RELEAS'

The query optimizer goes through a lot of extra work if a column allows nulls.
0
 

Author Comment

by:griffinj01
ID: 7008845
Thanks for the ideas but query govenor cost limit is 0 and all columns are not nullable.
0
 
LVL 1

Expert Comment

by:falconew
ID: 7010090
Hi,

When you run it without passing it into variable what is the output?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:griffinj01
ID: 7010448
The output is just a normal count.

eg.            
-----------
379

(1 row(s) affected)

 
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 1600 total points
ID: 7011251
Try this instead . . . .

declare @var int

select @var = count(*) from (
--**** following would be a view
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
    r2.[sequence] =
    CASE WHEN NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                    WHERE      r3.request_id = r1.request_id AND
                        r3.event_type = 'RELEAS' AND
                         r3.[sequence] > r1.[sequence]
                    )
         THEN ( SELECT MAX(r4.[sequence]) FROM request_event r4
                WHERE  r4.request_id = r1.request_id AND
                    r4.event_type = 'COMPLT' AND
                    r4.[sequence] > r1.[sequence]
               )
         ELSE      ( SELECT MIN(r5.[sequence]) FROM request_event r5
                WHERE  r5.request_id = r1.request_id AND
                    r5.event_type = 'RELEAS' AND
                    r5.[sequence] > r1.[sequence]
               )
         END
--***** end of view
) re
inner join request r on re.request_id = r.request_id


select @var
0
 

Author Comment

by:griffinj01
ID: 7011272
Excellent! Thanks spcmnspff. Do you want the points?

Why would that work and not the way I tried?
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7011307
Yeah I would like the points =).  Uhhh - My guess is that there were too many selects and the optimizer got confused, but really, who knows . . . .  One other thing, I was working on rewriting your view.  It would probably be faster with a union and a not exists rather than a cross join and a case.  Hold on I'll send you that too . . . .
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7011330
griffinj01:

I was thinking of something like this for th view:

SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
Where r2.[sequence] =  ( SELECT MIN(r5.[sequence]) FROM request_event r5
                   WHERE  r5.request_id = r1.request_id AND
                   r5.event_type = 'RELEAS' AND
                   r5.[sequence] > r1.[sequence]

    AND EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                  WHERE      r3.request_id = r1.request_id AND
                  r3.event_type = 'RELEAS' AND
                  r3.[sequence] > r1.[sequence])
   
UNION ALL
                   
SELECT r1.request_id, r1.event_type, r1.event_dt
from request_event r1, request_event r2
where
   r2.[sequence] = ( SELECT MAX(r4.[sequence]) FROM request_event r4
                     WHERE  r4.request_id = r1.request_id AND
                     r4.event_type = 'COMPLT' AND
                     r4.[sequence] > r1.[sequence]
              )  
   
   AND NOT EXISTS  ( SELECT r3.[sequence] FROM request_event r3
                   WHERE      r3.request_id = r1.request_id AND
                       r3.event_type = 'RELEAS' AND
                        r3.[sequence] > r1.[sequence]

It actually turns out to bo not much different than yours, but see if the reads improve with this one  . .  .
0
 

Author Comment

by:griffinj01
ID: 7011348
Cool - I'll give that a go.  Thanks very much.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7011416
Here is another possibility for the view.  I am not able to test it, but you might want to try it just in case:

SELECT r1.request_id, r1.event_type, r1.event_dt
FROM request_event r1
INNER JOIN
(
SELECT r2.request_id, MAX(r2.[sequence]) AS maxSeq, MIN(r2.[sequence]) AS minSeq
FROM request_event r2
WHERE r2.request_id = r1.request_id AND
((r2.event_type = 'RELEAS' AND maxSeq > r1.[sequence]) OR
(r2.event_type = 'COMPLT' AND minSeq > r1.[sequence]))
GROUP BY r2.request_id
) AS r2e ON r1.request_id = r2e.request_id

You will need index(es) on request_id (very likely already present) and [sequence] (probably present as well).  For the purposes of this query, it would help if you added event_type as an additional column in (one of) the index(es); that is, if it's a large table.  Then the query will be able to complete using only the index, without accessing the primary table at all.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question