Tips and Tricks for:  SQL Server, VB, VB.Net, C#, COM, COM+, IIS, XML, etc.

The Problem:   Error 2801 was recorded in a SQL Agent Job

The following text was stored in the job history for a step in a SQL Server Agent job that had been running successfully for quite a long time:

Executed as user: BGEC\sql2ksvc. The definition of object 'usp_AppSession_Trace' has changed since it was compiled. [SQLSTATE 42000] (Error 2801) Associated statement is not prepared [SQLSTATE HY007] (Error 0) RC=0 SessionID = 4740 [SQLSTATE 01000] (Message 0).
The step failed.
 

I'm not sure what caused this although I think a replication subscription was being deleted at the time this happened.

Solution: Recompile all stored procedures..

This job is important to the success of our application and must be running at all times.  To get it back up and running I used sp_recompile on all the stored procedures invoked by the job.  I then sat down and wrote this stored procedure to recompile all stored procedures any time that I ran into a similar problem.

CREATE PROCEDURE dba_Proc_Recompile_All
    @Schema_Pattern nvarchar(776) = NULL -- Owner name Pattern for Like, Null for all
  , @Proc_Name_Pattern nvarchar(776) = Null -- Proc Pattern for Like, Null for all
/*
* Marks all stored procedures for recomilation.
*
* Example:
exec dbo.dba_Proc_Recompile_All default, '%'
****************************************************************/
AS
 
SET NOCOUNT ON
 
-- First do the user databases specified in the control table
DECLARE ProcCur CURSOR READ_ONLY FOR
            SELECT ROUTINE_SCHEMA
              , ROUTINE_NAME
            FROM INFORMATION_SCHEMA.ROUTINES
            WHERE 0=OBJECTPROPERTY(object_id(ROUTINE_SCHEMA + '.'
                                     + ROUTINE_NAME), 'IsMsShipped')
                        AND ROUTINE_TYPE = 'PROCEDURE'
                AND (@Schema_Pattern IS NULL
                       OR ROUTINE_SCHEMA LIKE @Schema_Pattern)
              AND (@Proc_Name_Pattern IS NULL
                       OR ROUTINE_NAME LIKE @Proc_Name_Pattern) 

DECLARE @Owner nvarchar(776)
      , @Proc  nvarchar(776)
      , @SQL   nvarchar(4000)
      , @NumP  int
      , @NumE  int
      , @myError int
      , @myRowcount int 

SELECT @NumP = 0 , @NumE = 0

OPEN ProcCur
FETCH NEXT FROM ProcCur INTO @Owner, @Proc 

WHILE (@@fetch_status <> -1) BEGIN
    IF (@@fetch_status <> -2) BEGIN
        SELECT @SQL = 'exec sp_recompile ''' + @Owner + '.' + @Proc + ''''
        PRINT 'Running: ' + @SQL
        EXEC (@SQL)
        SELECT @myError = @@Error       
        IF @myError = 0 BEGIN
              SET @NumP = @NumP + 1
            END
        ELSE BEGIN
             PRINT 'Error Marking ' + @Owner + '.' + @Proc + ':' + convert(varchar(20), @myError)
             SET @NumE = @NumE + 1
        END
    END

   FETCH NEXT FROM ProcCur INTO  @Owner, @Proc
END

CLOSE ProcCur
DEALLOCATE ProcCur