Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Undocumented Routines: Forbidden fruit.


Undocumented Routines: Forbidden fruit.

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
mfaisal (11/25/2008)
Dear......

i am found of SQL study. you help me what can i do it?



Please post unrelated questions in a new thread.


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


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44806 Visits: 39848
Heh... "Don't use undocumented features"... as opposed to what? Using just the documented ones? You mean like the wonderful and heavily documented sp_MakeWebTask whose required privs changed overnight and without warning when they shifted for 2k sp3a to sp4? Or how about when the heavily documented SUM() OVER is finally updated to meet ANSI standards so we can actually use it to do a running total?

That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.

And then, there are the undocumented features that have already been discussed. I'll also add in the wonderfull xp_DirTree (3 operand version) and a couple of others that seem to be cast in stone. They've been there for at least 3 versions of SQL Server... why Microsoft doesn't document them, one can only guess.

To me, saying "Don't use undocumented features" is like saying "Write only ANSI code". There's a lot of things that just wouldn't get done and SQL Server would truly be "just a place to store data"... or worse... it would look and work (I hate it) like Oracle. Tongue

And, all you Oracle guys who are going to get all up in arms about what I just said about Oracle... lemme ask you... how do YOU return a result set from a stored procedure to a GUI. Ok... you can sit back down. Hehe

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
Jeff Moden (1/31/2009)
...work (I hate it)...

Heh. I wouldn't spread that around the office, Jeff. BigGrin

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8839
Jeff Moden (1/31/2009)

That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.


If there are bugs in the system, I'm glad they (ms) fix it.
Maybe the fix isn't what we would like it to be, but if it closes weak parts of the implementations, they should post a remark for this behaviour, so we can prepare for the impact.

And because we all have a nice system of Test -> QA -> EnduserTest -> Prod BigGrin , this kind of problems should arrise before it shuts down any production revenues.

What is important, is that you know you are using an undocumented whatever and that you have to check its availability / operational status with every sp, hotfix, new realease, ... and be prepared to pay the price if they throw it away.

So first try to figure out the proper way before going to any darker side. Wink

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 2937
Yeah. spMakeWebTask. I love using this for dishing out excel-based reports via email. I still use it, but with a delegation process for doing daily reports (I'm too idle to use Service Broker) It now, sadly has a sentence of deprecation on it which makes me slightly irritated.

Robyn and I describe a delegation system on http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/

Jeff, there was a security issue designed-into spMakeWebTask. Actually, as well as that it used to kill the server intermittently under heavy use when I first used it in v7 . (They fixed that in 2000).

There is nothing quite so useful as spMakeWebTask for the busy programmer doing lots of reporting. I don't quite buy the idea that SQL Server 2005 Reporting Services (SSRS) is as handy. One day, maybe I can persuade Robyn to do a workbench on it, along with a nifty CLR that does the same job.


Best wishes,

Phil Factor
Simple Talk
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44806 Visits: 39848
ALZDBA (2/1/2009)
Jeff Moden (1/31/2009)

That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.


If there are bugs in the system, I'm glad they (ms) fix it.
Maybe the fix isn't what we would like it to be, but if it closes weak parts of the implementations, they should post a remark for this behaviour, so we can prepare for the impact.

And because we all have a nice system of Test -> QA -> EnduserTest -> Prod BigGrin , this kind of problems should arrise before it shuts down any production revenues.

What is important, is that you know you are using an undocumented whatever and that you have to check its availability / operational status with every sp, hotfix, new realease, ... and be prepared to pay the price if they throw it away.

So first try to figure out the proper way before going to any darker side. Wink


And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.

It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44806 Visits: 39848
Phil Factor (2/1/2009)
Yeah. spMakeWebTask. I love using this for dishing out excel-based reports via email. I still use it, but with a delegation process for doing daily reports (I'm too idle to use Service Broker) It now, sadly has a sentence of deprecation on it which makes me slightly irritated.

Robyn and I describe a delegation system on http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/

Jeff, there was a security issue designed-into spMakeWebTask. Actually, as well as that it used to kill the server intermittently under heavy use when I first used it in v7 . (They fixed that in 2000).

There is nothing quite so useful as spMakeWebTask for the busy programmer doing lots of reporting. I don't quite buy the idea that SQL Server 2005 Reporting Services (SSRS) is as handy. One day, maybe I can persuade Robyn to do a workbench on it, along with a nifty CLR that does the same job.


Yep... that's how we ended up fixing it... a proxy user/delegation system. "Works fine, fails safe, and drains to the bilge".

Again, my point is, it was fully documented and, although I admit the change in security was necessary to cover a security loop hole, the change had an impact just as if an undocumented feature had been used. The only difference is that they included the fact in the "what changed" section of the service pack documentation. Code still needed to be fixed. Like I said, wait until they fix the very well documented SUM() OVER... lot's of people are gonna have to fix a lot of code just as if an undocumented feature had changed. Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
Jeff Moden (2/1/2009)
Like I said, wait until they fix the very well documented SUM() OVER... lot's of people are gonna have to fix a lot of code just as if an undocumented feature had changed. Wink


Depends how they 'fix' it. If they leave the ORDER BY optional (contrary to the other windowing functions) and ensure that the default behavior remains as it is now, then there shouldn't need to be any code changes.


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


ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8839
Jeff Moden (2/1/2009)

And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.

It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.


Indeed, you cannot effort not to do an acceptable series of regression tests.

But by using undocumented features, you will get into troubles in the long run, and it's removal or alter will not be nor get documented or announced.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44806 Visits: 39848
ALZDBA (2/1/2009)
Jeff Moden (2/1/2009)

And THAT is what I'm really getting at... it doesn't matter if it's an undocumented feature or not! If you don't do any regression testing before you drop a "fix", sp, or whatever into your production system, you're begging for "Death by SQL" even if you haven't used any undocumented features.

It's not the use of undocumented features that will kill you... it's any change even on fully documented features. You must test before making any patch or sp to SQL Server or at least be very, very aware of what the impact will be.


Indeed, you cannot effort not to do an acceptable series of regression tests.

But by using undocumented features, you will get into troubles in the long run, and it's removal or alter will not be nor get documented or announced.



My point is, that also happens with fully documented features.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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