Archive

Posts Tagged ‘undocumented’

SQL Server – Monitoring/Controlling Windows Services using xp_servicecontrol

15.08.2011 1 comment

xp_servicecontrol is an undocumented extended stored procedure, which can be used to start/stop or check the state of Windows services. It has the following syntax:

xp_servicecontrol @Action = N'Action' @ServiceName = N'Service Name'

Arguments:

@Action can be any of the following:

start Starts the service if it is not running. If the service is already in running state, an error is raised.
stop Stops the service if it is running. If the service is not running, an error is raised.
pause Pauses a running service. An error is raised if the service is not running. Also, not all services support pausing.
continue Continues running a paused service. An error is raised if the serviced is not in paused state.
querystate Returns the current state of the service.

@ServiceName is the name of the service, Service Name is different than the Display name of service. You can check the service name from Services Management Console.

1. Launch Services Management Console –> Start –> Run –> "Services.msc",

2. Open Service Properties – Right-click the required service –> Select “Properties” from the context menu. You can find both display name and actual service name here:

image

 

Using xp_servicecontrol to check service state:

xp_servicecontrol can check state for any windows service using:

— Check Current State of any Service

 

— SQL Server (MSSQLServer)

EXEC xp_servicecontrol N'querystate',N'MSSQLServer'

— SQL Server Browser

EXEC xp_servicecontrol N'querystate',N'SQLBrowser'

— Tablet PC Input Service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Start/Stop/Pause/Continue a service using xp_servicecontrol:

Note: Not all services support paused state.

— Start/Stop Services

EXEC xp_servicecontrol N'start',N'SQLServerAGENT'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

WAITFOR DELAY '00:00:05'

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

Result Set:

Service Started.

Msg 22003, Level 1, State 0

 

(0 row(s) affected)

 

Current Service State

————————————————————————————
Starting…

 

(1 row(s) affected)

 

Current Service State

————————————————————————————
Running.

 

(1 row(s) affected)

SQL Server Agent service does not support paused state, so an error will be raised when below code is executed:

— Pause/Continue Services

EXEC xp_servicecontrol N'pause',N'SQLServerAGENT'

EXEC xp_servicecontrol N'pause',N'Netlogon'

EXEC xp_servicecontrol N'continue',N'Netlogon'

Result Set:

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1052, 'The requested control is not valid for this service.'

Service Paused.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Service Continued.

Msg 22003, Level 1, State 1

 

(0 row(s) affected)

Since Netlogon service does support paused state, pause/continue actions can be performed on the service.

 

When trying to start/stop a service, whose Startup mode is set to disabled, xp_servicecontrol will raise an error. On my system I have disabled Tablet PC Input Service, as I do not use pen devices to input:

— Disabled service

EXEC xp_servicecontrol N'querystate',N'TabletInputService'

 

EXEC xp_servicecontrol N'start',N'TabletInputService'

Result Set:

Current Service State

————————————————————————————
Stopped.

 

(1 row(s) affected)

 

Msg 22003, Level 16, State 1, Line 0

StartService() returned error 1058,

'The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.'

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

11.05.2011 No comments

We can use the xp_regwrite undocumented extended stored procedure to write new entries to Windows registry.

You need to provide the key path, A new key will be created if it does not exist.

The following code will add a new entry in startup programs list in registry:

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

EXEC master..xp_regwrite
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire',
     @type        = 'REG_SZ',
     @value       = 'C:Program FilesMicrosoft GamesSolitaireSolitaire.exe'

 

Output returned by the procedure will be '(0) rows affected', unless it encounters an error.

 

To verify the entry, you need to open Registry Editor and traverse to specified path.

 

image

 

Or you can also use xp_regread or xp_instance_regread to verify the entry.

 

You can delete an registry entry using xp_regdelete.

 

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

EXEC master..xp_regdeletevalue
     @rootkey     = 'HKEY_LOCAL_MACHINE',
     @key         = 'SoftwareMicrosoftWindowsCurrentVersionRun',
     @value_name  = 'Solitaire'

 

Output returned by the procedure will be '(0) rows affected', unless it encounters an error.

 

Hope This Helps!

Vishal