SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

edisonsql
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]
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?

TG
Flowing Fount of Yak Knowledge

USA
6062 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
why?

Be One with the Optimizer
TG
Go to Top of Page

edisonsql
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

robvolk
Most Valuable Yak

USA
15675 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

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000