too many UNIONs? (sql query throws exception)

[kreplech]

hello all... having trouble with a query. possibly too many UNIONs? - eats too much memory? - i don't know...

the error produced:
DATE/TIME: 4/30/2009 10:27:15 AM

DESCRIPTION: Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 67 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

COMMENT: (None)

JOB RUN: (None)



the query:

SELECT ID, 0 AS Empl_Rcd_Nbr, EFF_Date, 0 AS Eff_Sequence, JOBCODE, 'POS' AS ACTION, 'PRO' AS REASON, 'Y' AS Calculate_Comp, 0 AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Promo_Increase_Increment IS NOT NULL and Proposal_Base_Pay <= 198000)

UNION ALL

SELECT ID, 0 AS EMPL_Rcd_Nbr, EFF_Date, Promo_Flag + Mkt_Flag - 1 AS EFF_Sequence, JOBCODE, 'PAY' AS Action, 'ADJ' AS Reason,
'Y' AS Calculate_Comp, Mkt_Adj_Increase_Increment AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Mkt_Adj_Increase_Increment IS NOT NULL and Proposal_Base_Pay <= 198000)

UNION ALL

SELECT ID, 0 AS EMPL_Rcd_Nbr, EFF_Date, Promo_Flag + Mkt_Flag + Merit_Flag - 1 AS EFF_Sequence, JOBCODE, 'PAY' AS Action, 'MER' AS Reason,
'Y' AS Calculate_Comp, Merit_Increase_Increment AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Merit_Increase_Increment IS NOT NULL and Proposal_Base_Pay <= 198000)

UNION ALL

SELECT ID, 0 AS EMPL_Rcd_Nbr, EFF_Date, Promo_Flag + Mkt_Flag + Merit_Flag + Structural_Flag - 1 AS EFF_Sequence, JOBCODE, 'PAY' AS Action,
'STR' AS Reason, 'Y' AS Calculate_Comp, Structural_Increase_Increment AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Structural_Increase_Increment IS NOT NULL and Proposal_Base_Pay <= 198000)

UNION ALL

SELECT ID, 0 AS EMPL_Rcd_Nbr, EFF_Date, Promo_Flag + Mkt_Flag + Merit_Flag + Structural_Flag + Promo_Flag - 1 AS EFF_Sequence, JOBCODE,
'PAY' AS Action, 'PRO' AS Reason, 'Y' AS Calculate_Comp, Promo_Increase_Increment AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Promo_Increase_Increment IS NOT NULL and Proposal_Base_Pay <= 198000)

UNION ALL

SELECT ID, 0 AS EMPL_Rcd_Nbr, EFF_Date, Promo_Flag + Mkt_Flag + Merit_Flag + Structural_Flag + Promo_Flag + LS_Flag - 1 AS EFF_Sequence,
JOBCODE, 'PAY' AS Action, 'LSM' AS Reason, 'Y' AS Calculate_Comp, Adjusted_Lump_Sum AS Change_Amount
FROM dbo.qry_Upload_Actions_ALL
WHERE (Adjusted_Lump_Sum IS NOT NULL and Proposal_Base_Pay <= 198000)

order BY ID, eff_Sequence


SQL error log doesn't match the one in the Microsoft KB article (

http://support.microsoft.com/kb/909089)

regarding hotfix for UNIONs... so I'm not going to install it.

SQL Server 2000 is running on a blade - multiple processors.

This query has worked in the past. Originally with UNION - then broke - so I switched to UNION ALL which worked for a short time - now here I am.

This is a memory issue on the server, no?

Thanks for any help!!!!

M

 

[markros]

If you create each one of your selects as table-valued function and then UNION ALL them - would it work?

 

[SkipVought]

I know that in some instances, using an alias in subsequent UNIONs will throw an error.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kreplech]

markros - can you elaborate a bit? do you mean just one tvf? or one tvf for each select statement?

i'm not sure the former is possible...

 

[kreplech]

wow...

so, the second select statement returned 0 records. just for kicks, i commented it out - leaving the remaining 5 select statements (all of which have a result set) uncommented. the stinkin thing works now.

anybody ever hear of anything like that???!!!

it's gotta be a fluke. testing...

 

[markros]

I meant TVF for each statement separately and then use

select * from dbo.Fun1(198000)
union all
select * from dbo.Fun2(198000)

etc.

 

[RyanEK]

That error could be the result of your computer running low on memory. Check out this link MS Support

Ryan

 

[kreplech]

the query works just fine from a sproc. so, i've created a sproc that truncs then pops a table. the asp page now execs the sproc then selects from the table.

it's just a temporary workaround until we better organize our data. it's not terribly quick, but that nasty UNION table took forever to query from ASP anyway... so not too much difference there.

anyway, its a kludge that will get us by for a while.

thanks for the help!