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 ««1234»»»

Undocumented Routines: Forbidden fruit. Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 9:13 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 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 #608445
Posted Saturday, January 31, 2009 6:36 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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. :P

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.


--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."

(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 #647598
Posted Saturday, January 31, 2009 10:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
Jeff Moden (1/31/2009)
...work (I hate it)...

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


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #647657
Posted Sunday, February 1, 2009 6:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 AM
Points: 6,733, Visits: 8,485
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 :D , 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. ;)


Johan


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #647689
Posted Sunday, February 1, 2009 8:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 588, Visits: 2,555
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
Post #647702
Posted Sunday, February 1, 2009 8:26 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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 :D , 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. ;)


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."

(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 #647706
Posted Sunday, February 1, 2009 8:34 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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. ;)


--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."

(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 #647708
Posted Sunday, February 1, 2009 10:03 AM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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. ;)


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 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 #647719
Posted Sunday, February 1, 2009 11:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 AM
Points: 6,733, Visits: 8,485
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #647729
Posted Sunday, February 1, 2009 7:00 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(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 #647771
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse