Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'

I have started getting this new error after installing SQL 2005 service pack 1 whenever I start the SQL agent :

Log SQL Agent (Current - 6/5/2006 12:28:00 AM)
Message
[298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)

I don;t see any problems because of it but would like to fix it because it is an error and might be affecting something I don't know about.

Thank you.

please post this question to the "SQL Server Tools General" forum, as someone should be able to help you there.

Thanks,

Peter

|||

I had the same issue. Solved it by excluding the system databases (like msdb) from the backups made in the SQL Maintenance plans executed by the SQL Server Agent / Integration Services.

Hope this helps,

Ries Vriend

http://www.amsterdamsoftware.com

|||I am not backing up any of the system databases and still getting this error.|||

I too am getting an error about the xp_sqlagent_notify in the SQLAgent log. Neither am I backing up the master, msdb nor model. Nor am I trying to send an email notification, just writing to the log.

The backup is running and I'm seeing the backup files, but the errors are troubling.

I'm running 32 bit SP1. SQL Server 9.0.2047

|||This error is showing up in our logs too. And we're not backing up MSDB or any system databases on this dev box.|||

I don't know if this has *anything* to do with your situation, but see if Agent extended procedures are enabled ...

select value, value_in_use from sys.configurations where name = 'Agent XPs'

|||yeah it is enabled. Still then Im getting the error.|||

I am also having a problem with this:

2006-10-26 08:52:42 - ! [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'.

The againt will not even start.

Anyone have any luck with this please let me know.

Thanks

Stacey

|||

We have heard a lot of feedback on this issue. Two bugs were filed in Microsoft Connect (http://connect.microsoft.com/SQLServer/). You can see the results here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=206727
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=142208

This item should be fixed in the next service pack (Service Pack 2). Please let us know if it is not.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||These links don't seem to go anywhere...is there a work around or fix? Post the answer, no links please :)|||

So, when will SP2 or a hotfix be released? It would be nice to have database mail working.

Thanks.

|||

SQL 2005 Service Pack 2 CTP fixed this on my server.

http://www.microsoft.com/sql/ctp.mspx#E2

|||Is this a show stopper for Database mail not to work? I can send test mail. but if I configure to send a mail when replication fails, it does not work and this seems to be the only error in the log|||

I had a problem like this with SQL Server 2005 and WSS 2.0. The server name has a dash in it, and when SharePoint created its databases, dashes were inserted. When running the following SQL command:

exec sp_msforeachdb "select '?';use ?; select object_name(object_id) n from ?.sys.sql_modules where definition like
'%msdb.dbo.xp_sqlagent_notify%'"

the query failed when it reached the database name that contained a dash. After renaming the database and doing all necessary changes to get SharePoint running again, I took the offending db offline and restarted server agent with no error.