Modifying a system stored procedure

Question

Modifying a system stored procedure

masterjd

SSC Veteran

Points: 280
More actions
June 12, 2013 at 2:37 pm

#278560

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]
GO
/****** Object: StoredProcedure [sys].[sp_dbmmonitorhelpmonitoring] Script Date: 06/12/2013 13:16:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_dbmmonitorhelpmonitoring]
as
begin
set nocount on
if (is_srvrolemember(N'sysadmin') <> 1 )
begin
raiserror(21089, 16, 1)
return (1)
end
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
begin
raiserror( 32049, 16, 1 )
return 1
end
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)
begin
raiserror( 32037, 16, 1)
return 1
end
select @freq_subday_interval update_period
return 0
end
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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

System stored procedures typically are NOT located in the MSDB database... not even the home grown ones. See if you can find it in the Master database. If it truly does work as you'd expect a system proc to work, then it has to be in the Master database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

masterjd (6/12/2013)
Here is an example of one of them.

That is not a user defined system proc.

http://msdn.microsoft.com/en-us/library/ms403828.aspx

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

Sean Pearce (6/13/2013)
masterjd (6/12/2013)
Here is an example of one of them.
That is not a user defined system proc.
http://msdn.microsoft.com/en-us/library/ms403828.aspx

You really should not change system stored procs at all. For one, any upgrades can and will overwrite your changes. It is better to wrap calls to system procs in user defined procs.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Sean Pearce (6/13/2013)
Sean Pearce (6/13/2013)
masterjd (6/12/2013)
Here is an example of one of them.
That is not a user defined system proc.
http://msdn.microsoft.com/en-us/library/ms403828.aspx
You really should not change system stored procs at all. For one, any upgrades can and will overwrite your changes. It is better to wrap calls to system procs in user defined procs.

Hmmm... I didn't check to see if it was an actual system stored procedure. Good catch. I absolutely agree. Never change system stored procs that were provided by MS. It's a form of "Death by SQL".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

masterjd SSC Veteran Points: 280 More actions · Answer 5

you guys are right - it's not a user-defined procedure at all. The reason I wanted to edit it was because it is used in one of the SQL Server Agent jobs, and I wanted to raise an error in the procedure to test email notifications on job failure. But now I know I should probably just copy the procedure to my own user-defined procedure, and change the job to use that for testing...