Solved

Internal SQL Server Error - Subquery and join

Posted on 2002-05-14
10
430 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
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
 

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 400 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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:ScottPletcher
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now