TIP: The names of the Microsoft extended stored procedures begin with the characters xp_, unlike the names of the standard system stored procedures, which begin with the characters sp_.
sp_addextendedproc is a SQL Server stored procedure used to register an extended stored procedure with SQL Server. The command must be executed by the sa in the master database. The syntax for the command is as follows:
sp_addextendedproc function, dll
In this syntax, function is the extended stored procedure function name and dll is the name of the DLL containing the function.
sp_dropextendedproc is a SQL Server stored procedure used to remove (drop) an extended stored procedure from SQL Server. The syntax for the command is as follows:
sp_dropextendedproc function
In this syntax, function is the name of the extended stored procedure to remove.
sp_helpextendedproc displays the extended stored procedures on a SQL Server. The syntax is as follows:
sp_helpextendedproc
Figure B.1 shows the output from sp_helpextendedproc. Note that the function and DLL name are displayed.
Figure B.1.
Sample output from sp_helpextendedproc.
The xp_cmdshell extended stored procedure executes a command string as a system command shell and returns any output.
TIP: xp_cmdshell is extremely useful for executing commands from stored procedures or triggers. If you need to execute several commands or a single command with a long command string, place the commands in a batch file (with the extension BAT) and execute the batch file.
The syntax for xp_cmdshell is as follows:
xp_cmdshell command[, no_output]
In this syntax, command is the command to execute and no_output is an optional parameter that tells the server to execute the command but not to return any output.
NOTE: If you use xp_cmdshell to start an application or batch file that does not return immediately, the connection issuing the extended stored procedure will block and wait until the application exits or the batch file completes (even if you use the no_output flag).
CAUTION: xp_cmdshell can execute any Windows NT command that SQL Server has permission to execute. If users are given permission to use xp_cmdshell, they can execute any commands that SQL Server has permission to execute.
xp_logevent logs user messages to the Windows NT event log and/or the SQL Server error log. The syntax is as follows:
xp_logevent error_number, User_Message, [Event_Log_Severity]
In this syntax, error_number is a number between 50,001 and 2,147,483,647. User_Message is a user-defined message up to 255 characters. Event_Log_Severity is an optional parameter that can have the value informational, warning, or error (the default is informational).
TIP: xp_logevent can be used in triggers to audit table changes. Use xp_logevent for error reporting in stored procedures.
Added in SQL Server 6.0, xp_msver returns information about SQL Server. The syntax for xp_msver is as follows:
xp_msver [option]
In this syntax, option can be any of the parameters listed in the Name column shown in Figure B.2.
Figure B.2.
Sample output of the xp_msver command.
xp_sendmail can be used to send a mail message or query from SQL Server to several users using e-mail. xp_sendmail has the following syntax:
xp_sendmail recipient, [...recipientn,]]
{@message | @query | @attachments}
[, copy_recipients] [, blind_copy_recipients] [, subject] [, type]
[, attach_results] [, no_output] [, no_header] [, width]
[, separator] [, echo_error] [, set_user]
In this syntax, recipient is the user or list of users to which you want to send the mail. @message or @query or @attachment is the body of the mail message. If a query is specified, the results of the query are sent. copy_receipients and blind_copy_recipients are other users to include on the mail list to receive the message. subject is the subject of the message. type is used to send a custom message.
attach_results specifies that the results of the query should be included as an attached file instead of appended to the end of the mail message. no_output specifies not to return results to the mail client. no_header specifies not to include the column header information for a query. width sets the line width for the message.
TIP: Use the extended stored procedure mail functions for error reporting and to send the results of nightly batch runs.
xp_snmp_getstate is new with SQL Server 6.5; it returns the state of the SQL Server SNMP (Simple Network Management Protocol) agent. The syntax is as follows:
xp_snmp_getstate [return_status OUTPUT]
In this syntax, return_status OUTPUT is a datatype int that holds the return status from the SNMP agent. When the extended stored procedure is executed without any parameters, the status and the definition of the state is returned in the result set.
xp_snmp_raisetrap is new with SQL Server 6.5; it allows a client to define and send an SNMP (Simple Network Management Protocol) alert (trap) to an SNMP client. The syntax is as follows:
xp_snmp_raisetrap server,database,error_message,
message_id,severity,user,comment,date_and_time,return_status OUTPUT
In this syntax, server is the name of the server on which the trap originated. database is the name of the database where the trap originated. error_message is the message for the trap (up to 255 characters). message_id is the message number associated with the error message. severity is the integer value of the SNMP trap.
user is the name of the user raising the SNMP alert (trap). comment is an additional message associated with the trap (up to 255 characters). date_and_time is the date and time at which the trap occurred. return_status OUTPUT is of datatype int and returns the completion status of the extended stored procedure.
xp_sprintf was added in SQL Server 6.0; it is similar to the C function sprintf. Use xp_sprintf to build an output string from a format list and a list of strings. The syntax is as follows:
xp_sprintf Build_String output, Build_String_Format [, string arguments]...
In this syntax, Build_String is the output string for the formatted string. Build_String_Format currently can be only %s (strings), and string_arguments is the list of arguments used to build the output string based on the format string.
xp sqlinventory is new with SQL Server 6.5; it collects domain information for SQL Server 6.5. The syntax is as follows:
xp_sqlinventory @DbName,@TableName,@Interval|stop
In this syntax, @DbName is the name of the database used to store broadcast information. @TableName is the table used to store the server information. @Interval is the amount of time in seconds used to check for broadcast messages. The stop parameter is used to stop all inventory actions in progress.
xp_startmail starts a SQL Server mail client session. xp_startmail has the following syntax:
xp_startmail [`mail_user_name'] [, `password']
In this syntax, mail_user_name is a valid mail user name and password is the password for the user.
xp_stopmail halts a SQL Server mail session. xp_stopmail has the following format:
xp_stopmail
The following extended stored procedures are used to set up integrated or mixed security. It is recommended that you use the SQL Security Manager instead of the extended stored procedures to configure security options.
xp_grantlogin adds a Windows NT user group or user to SQL Server. The syntax is as follows:
xp_grantlogin `NT_Account_Name' [, {`admin' | `repl' | `user'}]
In this syntax, NT_Account_Name is a Windows NT group or user. This optional parameter determines the type of SQL Server privileges assigned to the login: admin = SA, repl=repl_publisher, and user.
xp_revokelogin drops the user account or group from the SQL Server. The syntax is as follows:
xp_revokelogin `NT_Account_Name'
In this syntax, NT_Account_Name is a Windows NT group or user.
xp_logininfo provides SQL Server account access information. The syntax is as follows:
xp_logininfo [`NT_Account_Name'] [, `all' | `members'] [, variable_name]
In this syntax, NT_Account_Name is a Windows NT group or user. `all' reports all permission paths for the account, `members' reports information on all members of the group. variable_name is an optional output parameter that returns the permission level of the account (such as admin, repl, user).
xp_loginconfig reports SQL Server login security information. The syntax is as follows:
xp_loginconfig [`Config_Name_Parm']
In this syntax, Config_Name_Parm can be any of the parameters listed in the name column shown in Figure B.3.
Figure B.3.
Sample output from the xp_loginconfig command.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.