I am having a problem with a scheduled job I have set up.
The query involved runs for a week using an offset datepart.
On any day when there is no row returned then the job fails with a t-sql
subsystem error - seemingly because it can't calculate the null in the compute by section.
However, exactly the same query when run directly from query analyser just ignores the compute by, prints 'sum' with no figure beneath and continues to process the other days.
Looks a bit like this....
DECLARE @t_offset int
SELECT @t_offset = -1
WHILE @t_offset >-8
SELECT x , convert(varchar,dateadd(dayofyear, @t_offset, getdate()),106) as 'Date' , Count(*) as y
WHERE (convert(varchar,DateTime,106) like
convert(varchar,dateadd(dayofyear, @t_offset, getdate()),106) )
AND x IS NOT NULL
GROUP BY x
PRINT " "
SELECT @t_offset = @t_offset -1