Undocumented Features, which ones are still available in 2005?

Question

Undocumented Features, which ones are still available in 2005?

Jeff Moden

SSC Guru

Points: 1004436
More actions
August 7, 2006 at 9:21 am

#191778

The following SQLServerCentral article seems to indicate that xp_DirTree is still available in SQL Server 2005 as it was in SQL Server 2000....
http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp
Yep... I know the warnings about using undocumented features but xp_DirTree has saved my skin more than once in an ETL environment where xp_CmdShell is disallowed for use for the obvious security reasons...
There's talk in my shop that we may be upgrading from SQL Server 2000 SP4 to SQL Server 2005 in the near future... to know what I may have to find work-arounds for, does anyone know for sure if Master.dbo.xp_DirTree is still available in SQL Server 2005?
As a second question, does anyone have or know of a list of which undocumented features, such as xp_DirTree and xp_FileExist, are still available in SQL Server 2005?
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

it's still in 2005.

It's just a case of trying, although I would say that SSIS has much better features susch as support for wmi so thse things should be easier.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

Thanks, Colin,

Yeah, my problem with "just a case of trying" is we don't have it, yet... they keep threatening me with the offer of the Developer's Edition but I don't have it, yet.

I'm afraid you may have opened yourself up to a couple more questions (if you don't mind)... I'm a bit of a data troll and haven't had the opportunity to keep up to speed on all this... I can download the SQL Server 2005 Books Online from Microsoft but do you know if it covers SSIS and WMI?? And, what the heck are they, anyway?

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 3

You can find it in the downloads section at http://www.microsoft.com/sql. SSIS is SQL Server Integration Services and is the replacement for DTS. WMI is Windows Management... (something, totally slips my mind now), but is a set of tools for obtaining the status of and performing management functions on Windows machines.

BTW, in case you're curious, sp_helpextendedproc on my machine returns the following procedures in 2005:

sp_AddFunctionalUnitToComponent

sp_batch_params

sp_bindsession

sp_control_dbmasterkey_password

sp_control_plan_guide

sp_create_plan_guide

sp_createorphan

sp_cursor

sp_cursorclose

sp_cursorexecute

sp_cursorfetch

sp_cursoropen

sp_cursoroption

sp_cursorprepare

sp_cursorprepexec

sp_cursorunprepare

sp_delete_http_namespace_reservation

sp_droporphans

sp_enable_sql_debug

sp_execute

sp_executesql

sp_fetchLOBfromcookie

sp_fstr_getlocalnameforwinfs

sp_fstr_onfileclose

sp_fstr_onfileopen

sp_fulltext_freelist

sp_fulltext_getdata

sp_fulltext_keymappings

sp_fulltext_pendingchanges

sp_get_query_template

sp_getbindtoken

sp_getschemalock

sp_http_generate_wsdl_complex

sp_http_generate_wsdl_simple

sp_MSgetversion

sp_OACreate

sp_OADestroy

sp_OAGetErrorInfo

sp_OAGetProperty

sp_OAMethod

sp_OASetProperty

sp_OAStop

sp_PostAgentInfo

sp_prepare

sp_prepexec

sp_prepexecrpc

sp_refreshview

sp_releaseschemalock

sp_replcmds

sp_replcounters

sp_replddlparser

sp_repldone

sp_replflush

sp_replhelp

sp_replsendtoqueue

sp_replsetsyncstatus

sp_repltrans

sp_replwritetovarbin

sp_reserve_http_namespace

sp_reset_connection

sp_resyncexecute

sp_resyncexecutesql

sp_resyncprepare

sp_resyncuniquetable

sp_SetOBDCertificate

sp_setuserbylogin

sp_testlinkedserver

sp_trace_create

sp_trace_generateevent

sp_trace_setevent

sp_trace_setfilter

sp_trace_setstatus

sp_unprepare

sp_update_user_instance

sp_xml_preparedocument

sp_xml_removedocument

sp_xp_cmdshell_proxy_account

xp_adsirequest

xp_availablemedia

xp_cleanupwebtask

xp_cmdshell

xp_convertwebtask

xp_create_subdir

xp_delete_file

xp_deletemail

xp_dirtree

xp_dropwebtask

xp_enum_oledb_providers

xp_enumcodepages

xp_enumerrorlogs

xp_enumgroups

xp_fileexist

xp_findnextmsg

xp_fixeddrives

xp_get_mapi_default_profile

xp_get_mapi_profiles

xp_get_script

xp_get_tape_devices

xp_getnetname

xp_instance_regaddmultistring

xp_instance_regdeletekey

xp_instance_regdeletevalue

xp_instance_regenumkeys

xp_instance_regenumvalues

xp_instance_regread

xp_instance_regremovemultistring

xp_instance_regwrite

xp_logevent

xp_loginconfig

xp_makewebtask

xp_MSADEnabled

xp_MSADSIObjReg

xp_MSADSIObjRegDB

xp_MSADSIReg

xp_msver

xp_msx_enlist

xp_passAgentInfo

xp_prop_oledb_provider

xp_qv

xp_readerrorlog

xp_readmail

xp_readwebtask

xp_regaddmultistring

xp_regdeletekey

xp_regdeletevalue

xp_regenumkeys

xp_regenumvalues

xp_regread

xp_regremovemultistring

xp_regwrite

xp_replposteor

xp_runwebtask

xp_sendmail

xp_servicecontrol

xp_sprintf

xp_sqlagent_enum_jobs

xp_sqlagent_is_starting

xp_sqlagent_monitor

xp_sqlagent_notify

xp_sqlagent_param

xp_sqlmaint

xp_sscanf

xp_startmail

xp_stopmail

xp_subdirs

xp_sysmail_activate

xp_sysmail_attachment_load

xp_sysmail_format_query

xp_test_mapi_profile

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

Outstanding! That's just what I was looking for! Thank your for the help, Aaron.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)