Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 2008 modify system stored procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 06/12/2013 :  16:28:59  Show Profile  Reply with Quote
I have inherited maintenance of a SQL Server (2008), and I want to modify some of the system stored procedures. These are user-defined system stored procedures (for example: sys.sp_customproc). I can only assume they were created as system procedures so they could be shared across multiple databases? But regardless, I need to modify them.

Here is an example of one of them.

USE [msdb]
/****** Object:  StoredProcedure [sys].[sp_dbmmonitorhelpmonitoring]    Script Date: 06/12/2013 13:16:52 ******/
ALTER procedure [sys].[sp_dbmmonitorhelpmonitoring] 
set nocount on
if (is_srvrolemember(N'sysadmin') <> 1 )
        raiserror(21089, 16, 1)
        return (1)

declare @freq_type              int,    -- 4 = daily
        @freq_interval          int,    -- Every 1 days 
        @freq_subday_type       int,    -- 4 = based on Minutes
        @freq_subday_interval   int,    -- interval
        @job_id                 uniqueidentifier,
        @schedule_id            int,
        @retention_period       int,
        @jobname                nvarchar( 256 )

select @jobname   = isnull( formatmessage( 32047 ), N'Database Mirroring Monitor Job' )

select @job_id = job_id from msdb.dbo.sysjobs where name = @jobname
if (@job_id is null)    -- if the job does not exist, error out
    raiserror( 32049, 16, 1 )
    return 1 

select @schedule_id = schedule_id from msdb.dbo.sysjobschedules where job_id = @job_id
select  @freq_type = freq_type,
        @freq_interval = freq_interval, 
        @freq_subday_type = freq_subday_type,
        @freq_subday_interval = freq_subday_interval
    from msdb.dbo.sysschedules where schedule_id = @schedule_id

-- If the frequency parameters are not what we expect then return an error
-- Someone has changed the job schedule on us
if (@freq_type <> 4) or (@freq_interval <> 1) or (@freq_subday_type <> 4)
    raiserror( 32037, 16, 1)
    return 1

select @freq_subday_interval update_period

return 0

When I try to execute it, I get the error:

Msg 208, Level 16, State 6, Procedure sp_dbmmonitorhelpmonitoring, Line 46 Invalid object name 'sys.sp_dbmmonitorhelpmonitoring'.

My login is 'sa', I am mapped to the user 'dbo' in the [msdb] database. How do I modify this stored procedure?

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 06/12/2013 :  17:01:33  Show Profile  Reply with Quote
Looks to me like this is not a custom proc. It is an existing sql server provided system procedure. It is in Master (not msdb).

>>I need to modify them

Be One with the Optimizer
Go to Top of Page

Starting Member

2 Posts

Posted - 06/12/2013 :  17:28:21  Show Profile  Reply with Quote
ok i think you're right - this isn't a custom proc. The reason I want to modify it is because this procedure is called as part of a job defined in the SQL Server Agent, and I want to raise an error to test the email notifications for when the job fails.
Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 06/12/2013 :  17:32:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
You are far better off copying the code to a new procedure, making the modifications there, and changing the job to use the new procedure. Modifying Microsoft's code directly could leave you with an unusable database or server, and in most cases void your support agreement. You can't be sure if other system procedures use the code you're modifying and would subsequently break if it's changed.
Go to Top of Page

Patron Saint of Lost Yaks

30421 Posts

Posted - 06/12/2013 :  18:55:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can't use ALTER if the procedure is missing. Change ALTER to CREATE.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000