Archive

Posts Tagged ‘undocumented procedures’

SQL Server – Changing Default Database Location for Server

18.03.2013 2 comments

When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.

If you need to change this default location once SQL Server is installed, you can change this in server properties.

Method 1: Change default database location via SQL Server Management Studio:

Step 1. Right Click on Server and Select "Properties".

image

Step 2. in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. You can also change default backup location here.

image

Step 3. Click on "OK" to apply changes.

All new databases will be created to new location unless specified explicitly.

 

Method 2: Change default database location using TSQL Code:

You can also change default database location using TSQL code, use below code to change data, log and backup location:

USE [master]

GO

 

— Change default location for data files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultData',

       REG_SZ,

       N'C:MSSQLData'

GO

 

— Change default location for log files

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'DefaultLog',

       REG_SZ,

       N'C:MSSQLLogs'

GO

 

— Change default location for backups

EXEC   xp_instance_regwrite

       N'HKEY_LOCAL_MACHINE',

       N'SoftwareMicrosoftMSSQLServerMSSQLServer',

       N'BackupDirectory',

       REG_SZ,

       N'C:MSSQLBackups'

GO

Hope This Helps!

Vishal

SQL Server – Hide an Instance of SQL Server

23.08.2011 1 comment

You can browse available SQL Instances on network by choosing "<Browse for More..>" from Server Name drop-down list in "Connect to Server" dialog box in Management Studio:

image

As you can see from the screen shot, all of my instances are visible on the network. If I want to hide this information from people on my network, it can be done easily. SQL Server allows us to hide an instance from being listed on the network. This can be done using SQL Server Configuration Manager.

For example, If I need to hide my Developer Edition instance SQL01:

1. Launch SQL Server Configuration Manager

2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties

3. Set "Hide Instance" value to "Yes" from the drop-down list:

image

4. The Instance needs to be restarted in order for changes to take effect:

image

Once the instance is restarted it will not longer appear in the "Network Servers" list:

image

 

Hiding an Instance using T-SQL:

The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :

EXEC master..xp_instance_regwrite

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key =
N'SOFTWAREMicrosoftMicrosoft SQL Server
MSSQLServerSuperSocketNetLib',

      @value_name = N'HideInstance',

      @type = N'REG_DWORD',

      @value = 1

      — 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:

DECLARE @getValue INT

EXEC master..xp_instance_regread

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key=

N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLib',

      @value_name = N'HideInstance',

      @value = @getValue OUTPUT

SELECT @getValue

 

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.

 

Hope This Helps!

Vishal

Undocumented Extended Stored Procedures – XPs

14.05.2011 No comments

xp_fixeddrives is a useful extended stored procedure which returns amount of free space available in MB, for all local hard drives.

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_fixeddrives

drive MB free
—– ———–
C     91884
S     538

(2 row(s) affected)

It can be only used to check for free space on local drives, it won’t check mapped drives.

 

Another useful extended stored procedure is xp_fileexist, which can be used to check the existence of a file on file system.

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_fileexist 'C:WindowsExplorer.exe'

File Exists File is a Directory Parent Directory Exists
———– ——————- ———————–
1           0                   1

(1 row(s) affected)

xp_fileexist can also be used to check existence of a directory:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_fileexist 'C:WindowsSystem32'

File Exists File is a Directory Parent Directory Exists
———– ——————- ———————–
0           1                   1

(1 row(s) affected)

It also checks if the Parent Directory exists.

 

Hope This Helps!

Vishal

How to check SQL Server version – TSQL

13.05.2011 1 comment

The most common way to check SQL Server is to use @@VERSION configuration function. It returns version, architecture, OS version and build date for current instance.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT @@VERSION AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)
       Apr  2 2010 15:53:02
       Copyright (c) Microsoft Corporation
       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

(1 row(s) affected)

Another way is to use SERVERPROPERTY() metadata function. For a full list of properties that can be returned, check BOL.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT SERVERPROPERTY('ProductVersion') AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

 

You can also use extended stored procedures to check SQL Server version:


sp_MSgetversion:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..sp_MSgetversion

Character_Value
——————– ———– ———–
10.50.1600.1         1           3

(1 row(s) affected)


xp_msver:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_msver 'ProductVersion'

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
2      ProductVersion                   655410         10.50.1600.1

(1 row(s) affected)

To see the full list of properties returned by xp_msver, execute it without any arguments.

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_msver

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
1      ProductName                      NULL           Microsoft SQL Server

2      ProductVersion                   655410         10.50.1600.1

3      Language                         1033           English (United States)

4      Platform                         NULL           NT INTEL X86

5      Comments                         NULL           SQL

6      CompanyName                      NULL           Microsoft Corporation

7      FileDescription                  NULL           SQL Server Windows NT

8      FileVersion                      NULL           2009.0100.1600.01 ((KJ_RTM).100402-1540 )

9      InternalName                     NULL           SQLSERVR

10     LegalCopyright                   NULL           Microsoft Corp. All rights reserved.

11     LegalTrademarks                  NULL           Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12     OriginalFilename                 NULL           SQLSERVR.EXE

13     PrivateBuild                     NULL           NULL

14     SpecialBuild                     104857601      NULL

15     WindowsVersion                   393347078      6.0 (6002)

16     ProcessorCount                   2              2

17     ProcessorActiveMask              3              00000003

18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM

19     PhysicalMemory                   2041           2041 (2140626944)

20     Product ID                       NULL           NULL

(20 row(s) affected)

-– © 2011 – Vishal (http://SqlAndMe.com)


xp_instance_regread:

DECLARE @returnValue NVARCHAR(100)
EXEC   master..xp_instance_regread
       @rootkey              = N'HKEY_LOCAL_MACHINE',
       
@key                     = N'SOFTWAREMicrosoftMSSQLServerSetup',
       
@value_name       = N'Version',
       
@value                  = @returnValue output
SELECT @returnValue AS [Version]

Version
—————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

Hope This Helps!

Vishal

Stored procedures to retrieve directory listing – Undocumented XPs

12.05.2011 9 comments

You can use xp_subdirs and xp_dirtree undocumented stored procedures to retrieve a list of child directories under a specified parent directory from file system.

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_subdirs 'C:\Inetpub'

xp_subdirs – lists only directories which are direct children of the specified parent. In this case it returns directories which are directly under 'C:Inetpub' as below:

subdirectory

—————————

AdminScripts

custerr

history

logs

temp

wwwroot

 

(6 row(s) affected)

If you need to retrieve the children recursively, you will need to use xp_dirtree.

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_dirtree 'C:\Program Files\Microsoft.NET'

This returns all the children of 'C:Program FilesMicrosoft.NET' recursively, and their level from parent:

subdirectory                depth

——————–          ———–

ADOMD.NET                         1

100                               2

Resources                         3

1033                              4

110                               2

Resources                         3

1033                              4

Primary Interop Assemblies        1

RedistList                        1

 

(9 row(s) affected)

You can also restrict the number of level retrieved using:

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_dirtree 'C:\Program Files\Microsoft.NET', 2

This will only retrieve children of 'C:Program FilesMicrosoft.NET' whose level <= 2

subdirectory               depth
——————–             ———–
ADOMD.NET                         1
100                               2
110                               2
Primary Interop Assemblies        1
RedistList                        1

(5 row(s) affected)

Note: These procedures are undocumented and unsupported by Microsoft and they may *disappear* from newer SQL Server versions!

Hope This Helps!

Vishal

Accessing Registry using XPs (contd..) – TSQL

10.05.2011 3 comments

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues and xp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

— © 2011 – Vishal (http://SqlAndMe.com)

 

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWAREMicrosoftMSSQLServerSetup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServer]

this returns:

C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQL

 

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

 

C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQL

 

If the key path cannot be translated to instance-specific path, it will read from the specified key path.

 

Hope This Helps!

Vishal

Accessing Registry using XPs – TSQL

09.05.2011 1 comment

You can use the undocumented extended stored procedure xp_regread to access registry entries using T-SQL.

Syntax:

xp_regread    @rootkey      = N'rootkey',
              @key          = N'key',
              @value_name   = N'value_name',
              @value        = @outputValue OUTPUT

 

For example, below code returns "C:Program Files" on my system :

DECLARE @returnValue NVARCHAR(100)
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\MicrosoftWindows\CurrentVersion',
       @value_name   = N'ProgramFilesDir',
       @value        = @returnValue output
SELECT @returnValue

 

xp_regread ca only be used to retrieve a single value. If you need to retrieve multiple values, you will need to use xp_instance_regenumvalues, which returns all values under a specified key.

 

For example, To retrieve a list of start-up programs from registry we will use:

 

EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\Microsoft\Windows\CurrentVersion\Run'

 

this will return all entries under HKLMSoftwareMicrosoftWindowsCurrentVersionRun:

 

image 

 

Hope This Helps!

Vishal