Extended stored procedures provide a way to dynamically load and execute a function within a dynamic-link library (DLL) in a manner similar to that of a stored procedure, seamlessly extending SQL Server functionality. Actions outside of SQL Server can be easily triggered and external information returned to SQL Server. Return status codes and output parameters (identical to their counterparts in regular stored procedures) are also supported. SQL Server includes system stored procedures that add (sp_addextendedproc), drop (sp_dropextendedproc), and provide information about (sp_helpextededproc) extended stored procedures.
For better performance, extended stored procedures are implemented as DLLs rather than as separate processes. A separate process requires a context switch as well as additional security overhead. Instead, the DLL is executed by the user thread and is part of the SQL Server process, and therefore shares its address space and Windows NT system security privileges. However, the DLL does not know of, or have direct access to, SQL Server data structures.
Extended stored procedures are categorized by their use. The categories of extended procedures are:
These are the Integrated Security extended stored procedures:
| xp_enumgroups | xp_logininfo |
| xp_grantlogin | xp_revokelogin |
| xp_loginconfig |
These are the SQL Mail extended stored procedures:
| xp_deletemail | xp_sendmail |
| xp_findnextmsg | xp_startmail |
| xp_readmail | xp_stopmail |
These are the general extended stored procedures:
| xp_cmdshell | xp_sprintf |
| xp_logevent | xp_sscanf |
| xp_msver |
A number of additional extended stored procedures exist for internal use by other stored procedures (replication, system, and so on). Although these procedures can be executed separately, they are not guaranteed to be included in future releases.
Additional extended stored procedures can be created by programmers using Microsoft Open Data Services. For information about creating extended stored procedures, see Microsoft SQL Server Programming Open Data Services.
An additional level of security is added to the DLL by the SQL Server exception handler, which prevents the entire SQL Server from being stopped if an extended stored procedure causes a protection violation (typically, only that thread would be affected). SQL Server is protected from DLLs that might affect memory or harm the running SQL Server process. However, because SQL Server and all DLLs do share address space, there is always some small risk that a poorly written or insufficiently tested DLL could disrupt the entire system. It is important that the DLL (and all extended stored procedures within it) be well written and tested extensively. For this reason, only the SA can add extended stored procedures (using sp_addextendedproc).
Important Because extended stored procedures are external to SQL Server, they must be specified in the case (lowercase for all SQL Server-supplied extended stored procedures) in which they were designed.
Each of the following sections describe these extended stored procedures.