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 ( 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
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 ( 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