SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to modify 'system stored procedure' SQL 2008?


How to modify 'system stored procedure' SQL 2008?

Author
Message
Leon . Orlov .
Leon . Orlov .
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 261
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
jshailendra
jshailendra
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 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?
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14736 Visits: 9518
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."
Leon . Orlov .
Leon . Orlov .
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 261
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87265 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14736 Visits: 9518
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."
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10072 Visits: 5314
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
Dugi
Dugi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 3511
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! w00t

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32320 Visits: 18552
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86041 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search