• subscribe
May 18, 2005 12:00 AM

Believe It When Microsoft Tells You Not to Use Undocumented Functions and Behaviors

SQL Server Pro
InstantDoc ID #46216

SQL Server has always had nifty undocumented functions that are tempting to use. However, Microsoft has always urged users not to use those functions because they can change in future releases. For the most part, Microsoft hasn't changed undocumented behavior in a way that would cause problems if you had been sneaky and used some of the hidden features. SQL Server 2000 Service Pack 4 (SP4), however, will add limitations on what you can do with the undocumented extended stored procedures xp_regread and xp_regwrite. Perform an Internet search for xp_regread or xp_regwrite and you'll find dozens of sites that will teach you how to use those procedures to read from and write to the registry. Starting in SP4, these procedures will work with only SQL Server's registry keys. I'm sure there are many people using these extended procedures who won't be pleased with the change, but we were warned. Historically, SQL Server users could get away with ignoring the don't-use-an-undocumented-feature rule. Using the features rarely burned us. Now, we'll have to change our way of thinking and stop using undocumented and unsupported features in our production code.



ARTICLE TOOLS

Comments
  • JOHN KEITH
    6 years ago
    Nov 01, 2006

    Good thing the CLR is in SQL 2005. You can still read the registry due to .Net. Still better yet, the CLR can read remote registries if you know what you're doing. Muha hah hah ahahahahaaahahahhahahhahahah!

  • ROSS
    7 years ago
    Aug 15, 2005

    Here is the Microsoft knowledge base article with a "fix".

    http://support.microsoft.com/kb/887165/

  • ROSS
    7 years ago
    Aug 12, 2005

    It appears that xp_regread still works for all registry keys as long as you have been granted the "System Administrator" Server Role.

    I used the following example:
    declare @DomainName varchar(50)
    exec master.dbo.xp_regread 'HKey_Local_Machine', 'System\\CurrentControlSet\\Services\\TCPIP\\Parameters', 'Domain', @DomainName output
    select @DomainName

    For a user that does not have the "System Administrators" Server Role the following error is received:
    Server: Msg 22001, Level 16, State 1, Line 0
    xp_regread() returned error 5, 'Access is denied.'

  • Anonymous User
    7 years ago
    Jul 28, 2005

    Well it would be nice if there was some other way of telling which physical node in a cluster an instance is running on than using xp_regread. Maybe if MS is going to cut this stuff off then they will provide approved ways of getting important useful information.

You must log on before posting a comment.

Are you a new visitor? Register Here
  • SP1?
    I know there is a SP1 for SQL 2008 R2 available....and there is a "feature pack" as well... ...
  • SQL database mirroring
    I have SQL Server 2008 R2 Enterprise 64bit on Windows 2008 R2 Enterprise 64bit.  Each SQL Server has...
  • Dell Compellent Disk Drive
    Does anybody has experience with Dell Compellent Disk Drive? Basically, this system manages all disk...
  • Sql server performance tuning
    I need to find a tool that help me to optimize sql server,queries,improve the performance and solve ...