Warning: this is the actual code generated from my system:
;WITH RESULTS AS (
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'CountFocusRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'CountBiminiRecords' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
GROUP BY c.APPL_CD, c.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, m.APPL_CD, m.ALBASE, 'RecordsInFocusMissingInBimini' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
LEFT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
AND m.[MONTH] = c.[MONTH]
AND m.[BANK_NO] = c.[BANK_NO]
AND m.[COST_CENTER] = c.[COST_CENTER]
AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
AND m.[APPL_CD] = c.[APPL_CD]
AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = m.APPL_CD
AND cat.ALBASE = m.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE c.[YEAR] IS NULL
GROUP BY m.APPL_CD, m.ALBASE
UNION
SELECT 1174 AS BatchRunID, 'STATINV' AS Program, c.APPL_CD, c.ALBASE, 'RecordsInBiminiMissingInFocus' AS Measure, COUNT(*) AS Value
FROM [MISWork].[SX_FOCUS_NATIVE_200806] AS m WITH(NOLOCK)
RIGHT JOIN [MISWork].[SX_STATINV] AS c WITH(NOLOCK)
ON m.[YEAR] = c.[YEAR]
AND m.[MONTH] = c.[MONTH]
AND m.[BANK_NO] = c.[BANK_NO]
AND m.[COST_CENTER] = c.[COST_CENTER]
AND m.[GLACCOUNT_NO] = c.[GLACCOUNT_NO]
AND m.[CUSTACCOUNT] = c.[CUSTACCOUNT]
AND m.[APPL_CD] = c.[APPL_CD]
AND m.[ALBASE] = c.[ALBASE]
INNER JOIN MISProcess.SXProcessCatalog AS cat WITH(NOLOCK)
ON cat.APPL_CD = c.APPL_CD
AND cat.ALBASE = c.ALBASE
AND COALESCE(cat.ProcessName, 'STATINV') = 'STATINV'
WHERE m.[YEAR] IS NULL
GROUP BY c.APPL_CD, c.ALBASE
) SELECT * FROM RESULTS ORDER BY Program, APPL_CD, ALBASE, Measure
The code just sits there, no locking or blocking.
The individual components of the UNION return in a few seconds each. The code works in general for checking the output results of all the other programs in the STAT group, but just halts for this one.
Remove the CTE, no effect, sits there for 30 minutes/an hour, however long you care to wait before cancelling.
Remove the UNION, and the 4 result sets return in 11 seconds, total of 19 records accross all 4 result sets.
Run just the first two together - works fine, run just the last 2 together, also fine. First 3 together, fine, too.
I've already modified the code to output these to a #temp table, for other requirements, so I'm just going to change it to output each to the #temp table in sequence, but I have never seen SQL just stop like that with no evidence of blocking or anything.