Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to modify 'system stored procedure' SQL 2008? Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 10:01 AM
Points: 31, Visits: 245
Hello all,

Have anybody were successful in attempt to modify SQL Server 2008 'system stored procedure'?

Lets say that for some really insane reason you want to modify 'system stored procedure' like "sys.sp_add..." in your fine MS SQL Server 2008 server... how one can go about it?

cheers,

~Leon
Post #867807
Posted Thursday, February 18, 2010 2:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
As per my understanding and exp one should never modify any system objects...if you really wish some modification why dont you copy the code of system SP to new SP and do modification you want in newly created SP?
Post #867815
Posted Thursday, February 18, 2010 9:27 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Leon Orlov-255445 (2/18/2010)
Hello all,

Have anybody were successful in attempt to modify SQL Server 2008 'system stored procedure'?

Lets say that for some really insane reason you want to modify 'system stored procedure' like "sys.sp_add..." in your fine MS SQL Server 2008 server... how one can go about it?

While I believe that it is possible, it should NOT be done because:

1. It's pretty hard to do.
2. It's dangerous to your Server.
3. It's not supported by Microsoft.
4. It's will void your warranty.
5. It's probably unnecessary.

Given all of the above, I will not be telling someone in the "Newbies" forum how to do something that they should not be doing and that could harm them or someone else.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #868149
Posted Thursday, February 18, 2010 11:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 10:01 AM
Points: 31, Visits: 245
Yes, you shouldn't mod sys procs... but remember "insane reason" to do so.

So, with SQL 2008, is it still possible and if so, how one be able to do so?

BTW. never mind voiding warranty, MS support, or any of the best practice guidelines for db server administrations and what not.

Post #868373
Posted Thursday, February 18, 2010 12:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 41,558, Visits: 34,479
As far as I'm aware, you can't modify the system procs and there is no reason, insane or otherwise, to try to do so.

Write your own procs, put them in master and mark them as system objects if you like, but stay away from the system objects.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #868401
Posted Thursday, February 18, 2010 2:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Leon Orlov-255445 (2/18/2010)
Yes, you shouldn't mod sys procs... but remember "insane reason" to do so.

Um, no. You should remember "Newbie", "dangerous" and "unnecessary". I would no more tell you how to do this than I would tell an 8-year-old how to load a gun over the Internet.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #868565
Posted Thursday, February 18, 2010 3:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
While there may be a way to do it, there is no good reason to do so, as has been stated several times. As well as the fact that as soon as you apply a service pack or even a patch you could easily lose your change.

I am 100% sure that there is a better way to do whatever you want to accomplish that doesn't require mods to system objects..

CEWII
Post #868597
Posted Thursday, February 18, 2010 4:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
GilaMonster (2/18/2010)
As far as I'm aware, you can't modify the system procs and there is no reason, insane or otherwise, to try to do so.

Write your own procs, put them in master and mark them as system objects if you like, but stay away from the system objects.


I vote for this ...this is the correct one for you, Leon!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #868668
Posted Thursday, February 18, 2010 5:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 20,483, Visits: 14,136
Gail, Barry, and Elliott have put it very succinctly.

If you really want slightly different performance from one of the system procs, create on of your own that fills that requirement or suggest a mod or new one be created to MS. If they find it to be useful in widescale, they may make a change or create a new one for future release. Emphasis is on may. It never hurts to ask - but take it to the owner of the product.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #868672
Posted Thursday, February 18, 2010 5:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 36,013, Visits: 30,300
I'll throw in my 2 cents, as well. Do not ever modify a system stored procedure. If you need some of the functionality with an "insane" change, then script out the proc, make the desired changes, and save it as a new proc.

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #868683
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse