?
Solved

Warning: Fatal error 3624 with massive union query

Posted on 2007-11-27
5
Medium Priority
?
1,130 Views
Last Modified: 2012-08-14
We have 3 SQL Server 2005 setups for development, test and live.  The database in question resides on all three servers.  One of our report writers has a massive union query that runs against this database.  The query works in dev, works in test but fails in live with the following fatal error (something interesting to note, when the I break the union up and run the top half and the bottom half seperate, it runs fine... which leads me to believe that it maybe a configuration issue since it works on the dev and test boxes):

Location:       xxchng.cpp:436
Expression:       pxteExchange->FMarkTypeStart()
SPID:             52
Process ID:       2000
Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 3624 occurred at Nov 27 2007 10:20AM. Note the error and time, and contact your system administrator.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I'm going to include his SQL for reference.
SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', SUM(credit_amount) AS unit_amount, 0 AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') AND credit_type_code = 'H'
	AND an.unit_code != 'ALUM'
	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')+1 ELSE YEAR('10/31/2007') END 
	AND credit_date <= ('10/31/2007')
	GROUP BY MONTH(credit_date), YEAR(credit_date)
 
UNION
	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, COUNT(DISTINCT td.transaction_id)AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') 
	AND an.unit_code != 'ALUM'	
	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')+1 ELSE YEAR('10/31/2007') END
	AND credit_date <= ('10/31/2007')
	GROUP BY MONTH(credit_date), YEAR(credit_date) 
 
 
UNION 
	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', SUM(credit_amount) AS unit_amount, 0 AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') AND credit_type_code = 'H'
	AND an.unit_code != 'ALUM'
	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END
	AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
	GROUP BY MONTH(credit_date), YEAR(credit_date)
 
UNION 
	SELECT MONTH(credit_date) AS 'Month', YEAR(credit_date) AS 'Year', 'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, COUNT(DISTINCT td.transaction_id)AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code AND transaction_category_code IN ('GF', 'PP', 'MG') 
	AND an.unit_code != 'ALUM'	
	AND fiscal_year = CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END
	AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
	GROUP BY MONTH(credit_date), YEAR(credit_date)
 
 
UNION
 
-- Unique Donor Counts Running Totals
	SELECT 
			'77' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'77' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 
UNION
	SELECT 
			'77' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('07/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'78' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'78' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 
UNION
	SELECT 
			'78' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('08/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'79' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'79' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 
UNION
	SELECT 
			'79' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('09/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'710' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'710' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'710' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('10/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'711' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'711' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'711' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('11/30/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'712' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'712' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'712' AS 'Month'
			,CASE WHEN MONTH('10/31/2007')  >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('12/31/' + (CAST(CASE WHEN MONTH('10/31/2007') >= 7 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-3 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-2),'10/31/2007')
 
UNION
	SELECT 
			'71' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('01/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'71' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('01/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
 
UNION
	SELECT 
			'72' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date >= (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
		AND credit_date <  (CONVERT(datetime,('03/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'72' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date >= (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <  (CONVERT(datetime,('03/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'73' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('03/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'73' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2  ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('03/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'74' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('04/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'74' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('04/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'75' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('05/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'75' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
						 AND (CONVERT(datetime,('05/31/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')
UNION
	SELECT 
			'76' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  
						(CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007') END AS VARCHAR)))))	
						 AND (CONVERT(datetime,('06/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007') ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
		AND credit_date <= ('10/31/2007')
UNION
	SELECT 
			'76' AS 'Month'
			,CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS 'Year'
			,'University' AS 'University', 0 AS unit_amount, COUNT(DISTINCT tp.person_id) AS donor_count, 0 AS gift_count
	FROM transaction_detail td, transaction_person tp, account_number an, unit_code uc, person p
	WHERE td.transaction_id = tp.transaction_id AND tp.person_id = p.person_id AND td.account_number = an.account_number AND an.unit_code = uc.unit_code 
		AND transaction_category_code IN ('GF', 'PP', 'MG') AND an.unit_code != 'ALUM'
		AND credit_date BETWEEN  (CONVERT(datetime,('07/01/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-2 ELSE YEAR('10/31/2007')-1 END AS VARCHAR)))))
				AND (CONVERT(datetime,('06/30/' + (CAST(CASE WHEN MONTH('10/31/2007') <= 6 THEN YEAR('10/31/2007')-1 ELSE YEAR('10/31/2007')-2 END AS VARCHAR)))))
		AND credit_date <= DATEADD(YEAR,(-1),'10/31/2007')

Open in new window

0
Comment
Question by:IUFITS
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20358575
0
 
LVL 12

Accepted Solution

by:
Ken Selvia earned 2000 total points
ID: 20358855
Not sure but here is a theory;

There is a limit of 256 tables in a single statement.

Multiplying 36 unions * 5 tables per select  + 4 worktables for GROUP BY's + any number of worktables for sorting may put you close to that limit.

Differences in dev/test/prod may depend on # of CPU's and how the query is run in parallel.

Looks like your select statements are exclusive of one another so you might as well use UNION ALL to eliminate an intermediate sort and work table.

You might also try OPTION (MAXDOP 1)  to see if that makes any difference.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 20359656
Adding the OPTION (MAXDOP 1) fixed the issue.  Am I correct that assuming that the parallization was causing the number of tables touched to rise and that setting this option decreased that number running at the same time?
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 20359756
Not as far as I can discover. Parallel operations require separate streams to be combined, but nothing I can find indicates these contribute to part of the 256 table limit.   I suspect this is a bug and MAXDOP 1 is a workaround.

You might set MAXDOP to 1/2 the # of CPU's rather than 1 to see some benefit of parallelism.

0
 
LVL 3

Author Comment

by:IUFITS
ID: 20359800
That's a great suggestion.  I also tried this testing and it did work.  Thank you again for the prompt response, you saved me much time researching.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 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