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 .
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1194 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
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80840 Visits: 9519
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 .
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 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 (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)

Group: General Forum Members
Points: 554893 Visits: 47749
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
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80840 Visits: 9519
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
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58574 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
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: 10268 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 Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145647 Visits: 18652
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
Learn Extended Events

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

Group: General Forum Members
Points: 512064 Visits: 44309
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