Procedure not found by SQL Server Agent [SQLSTATE 42000] (Error 2812)

Question

A stored procedure is called by the SQL Server Agent on a daily basis, which works fine most of the time. Strange enough, sporadically the procedure is not found and the agent returns:

Could not find stored procedure "dbo.myProc". [SQLSTATE 42000] (Error 2812)

The step is defined straight forward as follows:

Database: myDB
EXECUTE dbo.myProc

Nothing about the procedure itself or privileges of the agent's service account was changed?

Answer 1

Not sure this will be helpful, but just in case...

I had a similar issue. Several daily jobs which called a stored procedure began failing with the message 'Could not find stored procedure'.

This was running against a replica of production that we use for reporting. The report server had been moved to another server, and this seemed to break the jobs. But sometimes they did succeed.

I finally noticed that the daily job to restore the report server from a backup of prod was taking longer than usual, so when the jobs ran, the database was still offline awaiting restore completion. My remedy was to modify the jobs to run two hours later.