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