Appendix B: Extended Stored Procedures

In this appendix, I cover some of the documented and undocumented extended stored procedures. Extended stored procedures allow you to call a program written in C++ that can be called from T-SQL. These procedures expand the flexibility of T-SQL to allow you to interact with the Windows operating system. Most of the procedures in this section are undocumented and are not supported by Microsoft.

xp_availablemedia

The xp_availablemedia extended stored procedure lists the drives that are available to read and write data. To execute the procedure, use the following syntax:

master..xp_availablemedia

This outputs the name of the drive, the free space in bytes (shown as low free) and the type of drive. Some of the types of drives you can see in the following results are a floppy drive (1), a hard drive (2), and a writable CD-ROM (8):

name                           low free    high free   media type  ------------------------------ ----------- ----------- ----------  A:\                            884736      0           1 C:\                            1993347072  0           2 D:\                            1982103552  5           2 F:\                            679477248   0           8

xp_cmdshell

The xp_cmdshell extended stored procedure is a commonly used procedure to execute programs using T-SQL. For example, you can use the procedure to execute a DTS package by using the following syntax:

master..xp_cmdshell 'DTSRun /S "servername" /U "username" /P "password" /N "DTSPackageName"'

This outputs the following results:

output                                              --------------------------------------------------  DTSRun:  Loading... DTSRun:  Executing... DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1 DTSRun:  Package execution complete. NULL

If you don't want the procedure to produce any output, use the no_output parameter.

xp_dirtree

The xp_dirtree extended stored procedure reports all of the subdirectories under the specified root directory. For example, if you'd like to determine which directories exist under the C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP folder, use the following syntax:

master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'

This outputs all the directories under the specified directory as shown here:

subdirectory                                       depth        -------------------------------------------------- -----------  Northwind                                          1 TranBackups                                        2 Pubs                                               1

The depth column shows how deep under the specified directory the directory named in the subdirectory column is. If you don't want the depth column, use the xp_subdirs procedure with the same parameters.

xp_enum_activescriptengines

The xp_enum_activescriptengines procedure lists all the scripting languages installed on the server. To execute the procedure, use the following syntax:

master..xp_enum_activescriptengines

This outputs the following results, which may vary based on the installed languages:

Program ID                     Description                     ------------------------------ ------------------------------  XML                            XML Script Engine VBScript                       VB Script Language VBScript.Encode                VBScript Language Encoding JScript                        JScript Language JScript.Encode                 JScript Language Encoding

xp_enum_oledb_providers

The xp_enum_oledb_providers procedure outputs all the installed OLE DB providers on the server. To execute the procedure, use the following syntax:

master..xp_enum_oledb_providers 

This results in the following results (mine are abridged):

Provider Name              Provider Description -------------------------- ------------------------------------------  Microsoft.ISAM.OLEDB.1.1   Microsoft ISAM 1.1 OLE DB Provider MSDAORA                    Microsoft OLE DB Provider for Oracle DTSPackageDSO              Microsoft OLE DB Provider for DTS Packages MSOLAP                     Microsoft OLE DB Provider for Olap Service MSDASQL                    Microsoft OLE DB Provider for ODBC Driver ADsDSOObject               OLE DB Provider for Microsoft Directory MSDAOSP                    Microsoft OLE DB Simple Provider SQLOLEDB                   Microsoft OLE DB Provider for SQL Server Microsoft.Jet.OLEDB.4.0    Microsoft Jet 4.0 OLE DB Provider

xp_enumcodepages

The xp_enumcodepages procedure lists all the code pages installed on the server. To execute the procedure, use the following syntax:

master..xp_enumcodepages

This outputs the following results (mine are abridged):

Code Page   Character Set                            Description ----------- ---------------------------------------- ---------------------- 50932       _autodetect                              Japanese (Auto-Select) 51932       euc-jp                                   Japanese (EUC) 65001       utf-8                                    Unicode (UTF-8) 1258        windows-1258                             Vietnamese (Windows) 1252        iso-8859-1                               Western European

xp_enumdsn

The xp_enumdsn procedure outputs a list of DSNs set up on the server. To execute the procedure, use the following syntax:

master..xp_enumdsn

This outputs the following results:

Data Source Name                 Description                              -------------------------------- ---------------------------------------- MS Access Database               Microsoft Access Driver (*.mdb) dBASE Files                      Microsoft dBase Driver (*.dbf) Excel Files                      Microsoft Excel Driver (*.xls) Visual FoxPro Database           Microsoft Visual FoxPro Driver Visual FoxPro Tables             Microsoft Visual FoxPro Driver dBase Files - Word               Microsoft dBase VFP Driver (*.dbf) FoxPro Files - Word              Microsoft FoxPro VFP Driver (*.dbf) MQIS                             SQL Server LocalDB                          SQL Server

xp_enumerrorlogs

The xp_enumerrorlogs procedure outputs a list of the SQL Server error logs on the server, along with their creation dates and sizes. To execute the procedure, use the following syntax:

master..xp_enumerrorlogs

This outputs the following results:

Archive #   Date                                     Log File Size (Byte) ----------- ---------------------------------------- -------------------- 0           03/31/2001  16:29                        3913 5           03/20/2001  07:04                        31574 1           03/30/2001  01:00                        729 2           03/29/2001  22:11                        13371 3           03/28/2001  01:00                        3701 4           03/22/2001  10:20                        2654 6           03/18/2001  18:26                        205249

xp_enumgroups

The xp_enumgroups procedure lists all the Windows local groups on the server.
To execute the procedure, use the following syntax:

master..xp_enumgroups

If you run this procedure on a server other than Windows NT or 2000, you receive the following error:

This system extended procedure is not supported on Windows 95.

If you have Windows NT or 2000, you receive the results similar to the following (mine are limited to 50 characters for the comment):

group                   comment  ----------------------- --------------------------------------------------  Administrators          Administrators have complete and unrestricted acce Backup Operators        Backup Operators can override security restriction Guests                  Guests have the same access as members of the User Power Users             Power Users possess most administrative powers wit Replicator              Supports file replication in a domain Users                   Users are prevented from making accidental or inte

xp_fileexist

The xp_fileexist procedure determines if a specified file exists in the directory. It can also tell you if the specified variable is a directory. You can use this to trigger certain events. For example, you can use this procedure to test for the existence of a file before launching a DTS package. To execute the procedure to test the existence of a file called autoexec.bat, use the following syntax:

master..xp_fileexist 'c:\autoexec.bat'

This outputs the following results (a result of 1 means the file or directory exists):

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

xp_fixeddrives

The xp_fixeddrives procedure displays the fixed drives on the server and how much space in megabytes is available to each drive. You can use this to determine if there is enough space on a drive before creating a new database or launching a DTS package to load large amounts of data. To execute the procedure, use the following syntax:

master..xp_fixeddrives

This outputs the following results:

drive MB free      ----- -----------  C     1773 D     1386 E     12429 F     29276

xp_getfiledetails

The xp_getfiledetails procedure displays information about the specified file, including its size and creation date. To execute the procedure, use the following syntax:

master..xp_getfiledetails 'c:\autoexec.bat' 

This outputs the following results:

Alternate Name  Size   Creation Date Creation Time Last Written Date  --------------- ------ ------------- ------------- -----------------  (null)          221    19800101      0             19970327          Last Written Time Last Accessed Date Last Accessed Time Attributes   ----------------- ------------------ ------------------ -----------  134752            19991007           0                  128        

xp_get_MAPI_profiles

The xp_get_MAPI_profiles procedure will return a list of MAPI profiles that are available to SQL Mail. The procedure executes with no parameters:

master..xp_get_MAPI_profiles

If profiles exist, you will receive results that look something like this:

Profile name                       Is default profile ---------------------------------- ------------------ Microsoft Outlook Internet Setting 1

If there are no MAPI profiles installed, you will receive the following message:

L -28

Server: Msg 18030, Level 16, State 1, Line 0 xp_get_mapi_profiles: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

Note 

There is a sister procedure to xp_get_MAPI_profiles called xp_get_MAPI_default_profile that will return the default MAPI mail profile only.

xp_getnetname

The xp_getnetname procedure displays the computer name of the SQL Server you're connected to. To execute the procedure, use the following syntax:

master..xp_getnetname 

This outputs the following results:

Server Net Name  --------------- XANADU

xp_loginconfig

The xp_loginconfig procedure lists the security and login configuration for SQL Server. To utilize the procedure, use the following syntax:

master..xp_loginconfig

This outputs the following results:

name                         config_value                  ---------------------------- ----------------------------  login mode                   Mixed default login                guest default domain               BEDROCK audit level                  none set hostname                 false map _                        domain separator map $                        NULL map #                        -

You can also optionally list the individual configuration item if you only want selective information:

master..xp_loginconfig 'login mode'

xp_logevent

The xp_logevent procedure is a useful procedure that logs events to Event Viewer from T-SQL. To use the procedure, you have to provide an error number, followed by the error message, and finally the severity. Available severities are INFORMATIONAL, WARNING, and ERROR. User-defined error numbers begin at 50,001. For example, you can use the following syntax to log an event into Event Viewer:

master..xp_logevent 50001, 'Bad login occured', warning

xp_logininfo

The xp_logininfo extended stored procedure produces valuable information about which Windows users have rights to your SQL Server and the types of permissions they have. To execute the procedure, use the following base syntax:

master..xp_logininfo

This results in the following information:

account name            type     privilege mapped login name ----------------------- -------- --------- ------------------------  BUILTIN\Administrators  group    admin     BUILTIN\Administrators XANADU\bknight          user     user      XANADU\bknight

You can also gain information about individual Windows users or groups by specifying an individual user or group as a parameter:

master..xp_logininfo 'BUILTIN\Administrators'

xp_ntsec_enumdomains

The xp_ntsec_enumdomains procedure lists the domain that your Windows server is a member of. To execute the procedure, use the following syntax:

master..xp_ntsec_enumdomains

This outputs the following:

Domain                             ---------------------------------  XANADU

xp_readerrorlog

The xp_readerrorlog procedure returns the contents of the current error log. To execute the procedure, use the following syntax:

master..xp_readerrorlog

You can also pass the extended stored procedure a simple parameter of what error log you'd like to read. This number corresponds with the filename. For example, to read the file errorlog.5, you would use the following command:

master..xp_readerrorlog 5

xp_regdeletekey

The xp_regdeletekey procedure allows you to delete a key from your registry. Be especially careful when using this procedure as it will not warn you before the procedure deletes the entire key. To run the procedure, use the @rootkey parameter to specify the root registry key and then the @key parameter to designate the key you want to delete:

master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE',  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey' 

This outputs the following error if the registry key can't be found:

Msg 22001, Level 1, State 22001 RegDeleteKey() returned error 2, 'The system cannot find the file specified.'

If the key can be found, you receive no message.

xp_regdeletevalue

You can also delete individual data items inside a registry key by using the xp_regdeletevalue procedure. The procedure uses the same parameters as the xp_regdeletekey procedure, but has an added @value_name parameter, which is the data item name:

master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE',  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey', @value_name='NewKeyName'

xp_regenumvalues

The xp_regenumvalues lists all the registry data items and values in a given key. To use the procedure, use the following syntax:

master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',  'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\'

This results in the following:

Value                          Value                          Data  ------------------------------ ------------------------------ ------ RegisteredOwner                MSEmployee                     NULL Value                          Data         ------------------------------ -----------  SerialNumber                   -2082537408 Value                          Data                            ------------------------------ ------------------------------  CurrentVersion                 8.00.194 Value                          Data         ------------------------------ -----------  Language                       1033

xp_regread

The xp_regread procedure reads an individual registry key to determine its existence or to read a data item in the key. The @rootkey parameter is the root key in the registry, and the @key parameter is the individual key. To use the procedure, you must specify the registry key's root and key as shown here:

master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\'

By using these two variables, you are only testing the existence of the key, which results in the following:

KeyExist     -----------  1

You can also read individual data items inside the key by using the added @value_name parameter, which represents the name of the data item:

master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\',@value_name='defaultLogin'

This results in the following:

Value                          Data                            ------------------------------ ------------------------------  defaultLogin                   guest

xp_regwrite

The xp_regwrite procedure allows you to create a data item in your server's registry and optionally create a new key. To use the procedure, you must specify the root key with the @rootkey parameter and designate an individual key with the @key parameter. If the key doesn't exist, it is created. The @value_name parameter designates the data item and the @type parameter the type of the data item. Valid data item types include REG_SZ and REG_DWORD. The final option is the @value parameter, which assigns a value to the data item.

The following syntax adds a new key called NewKey, and creates a new data item under it called NewKeyName:

master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NewKey', @value_name='NewKeyName', @type='REG_SZ', @value='Test'

xp_subdirs

The xp_subdirs procedure displays all the subdirectories one level down from the specified directory. To execute the procedure, simply pass it the root directory you'd like to see:

master..xp_subdirs 'C:\Program Files\Microsoft SQL Server\'

This will output results similar to the following:

subdirectory ------------------------  80 MSSQL

If you specified an invalid directory, the following results will be displayed:

Server: Msg 22006, Level 16, State 1, Line 0 Error executing xp_subdirs: FindFirstFile failed!




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net