Stored procs breaking overnight

Question

We are running MS SQL 2005 and we have been experiencing a very peculiar problem the past few days.

I have two procs, one that creates an hourly report of data. And another that calls it, puts its results in a temp table, and does some aggregations, and returns a summary.

They work fine...until the next morning.

The next morning, suddenly the calling report, complains about an invalid column name.

The fix, is simply a recompile of the calling proc, and all works well again.

How can this happen? It's happened three nights in a row since moving these procs into production.

EDIT: It appears, that it's not a recompile that is needed of the caller (summary) proc. I was just able to fix the problem by executing the callee (hourly) proc. Then executing the summary proc. This makes less sense than before.

EDIT2: The hourly proc is rather large, and I'm not posting it here in it's entirety. But, at the end, it does a SELECT INTO, then conditionally, returns the appropriate result(s) from the created temp table.

Select      [large column list]
    into    #tmpResults
    From    #DailySales8
    Where   datepart(hour,RowStartTime) >= @StartHour
    and datepart(hour,RowStartTime) < @EndHour
    and datepart(hour, RowStartTime) <= @LastHour

IF @UntilHour IS NOT NULL 
    AND EXISTS (SELECT * FROM #tmpResults WHERE datepart(hour, RowEndTime) = @UntilHour) BEGIN
        SELECT      * 
            FROM    #tmpResults
            WHERE   datepart(hour, RowEndTime) = @UntilHour
END ELSE IF @JustLastFullHour = 1 BEGIN
        DECLARE @MaxHour INT
        SELECT @MaxHour = max(datepart(hour, RowEndTime)) FROM #tmpResults

        IF @LastHour > 24 SELECT @LastHour = @MaxHour

        SELECT      * 
            FROM    #tmpResults
            WHERE   datepart(hour, RowEndTime) = @LastHour

        IF @@ROWCOUNT = 0 BEGIN
            SELECT      * 
                FROM    #tmpResults
                WHERE   datepart(hour, RowEndTime) = @MaxHour
        END
END ELSE BEGIN
        SELECT * FROM #tmpResults
END

Then it drops all temp tables and ends.

The caller (Summary)

First creates a temp table #tmpTodaySales to store the results, the column list DOES MATCH the definition of #tmpResults in the other proc. Then it ends up calling the hourly proc a couple times

    INSERT #tmpTodaysSales
        EXEC HourlyProc @LocationCode, @ReportDate, null, 1


    INSERT #tmpTodaysSales
        EXEC HourlyProc @LocationCode, @LastWeekReportDate, @LastHour, 0

I believe it is these calls that fail. But recompiling the proc, or executing the hourly procedure outside of this, and then calling the summary proc fixes the problem.

Answer 1

What do the overnight maintenance plans look like, and are there any other scheduled overnight jobs that run between 2230 and 1000 the next day? It's possible that step in the maintenance plan or another agent job is causing some kind of corruption that's breaking your SP.

Answer 2

Two questions:

Does the schema of #DailySales8 vary at all? Does it have any direct/indirect dependence on the date of execution, or on any of the parameters supplied to HourlyProc?

Which execution of INSERT #tmpTodaysSales EXEC HourlyProc ... in the summary fails - first or second?