Microsoft SQL Server 2008 R2 Unleashed- P184:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1784 CHAPTER 44 Advanced Stored Procedure Programming and Optimization Table lists the general categories of extended stored procedures. TABLE Extended Stored Procedures Categories Category Description General extended procedures Provide general functionality. Perhaps the most useful is xp_cmdshell which executes external programs and returns the output from them as a result set. SQL Mail extended procedures Enable you to perform email operations from within SQL Server. SQL Server Profiler extended procedures Are used by SQL Server Profiler. They can also be used directly for instance to create a trace queue and start the trace from within a stored procedure. OLE automation procedures Allow SQL Server to create and use OLE automation objects. API system stored procedures Are undocumented extended stored procedures used by the API libraries. The server cursor functionality for instance is implemented as a set of extended stored procedures. Using xp_cmdshell One of the most useful and potentially dangerous extended stored procedures provided with SQL Server 2008 is xp_cmdshell. xp_cmdshell can execute any operating system command or program available on the SQL Server system as long as it is a console program that doesn t require user input. xp_cmdshell accepts a varchar 8000 or nvarchar 4000 value as the command string to be executed and it returns the results of the command as a single nvarchar 255 column. The full syntax of xp_cmdshell is as follows xp_cmdshell 1command_string no_output If the no_output option is specified the results from the command are not displayed. The following example uses xp_cmdshell to list the files in a directory on the SQL Server computer s hard disk EXEC xp_cmdshell DIR c . xp_cmdshell runs synchronously. Control is not returned to the SQL Server user session until the shell command completes. This is why you have to ensure that the shell command invoked via xp_cmdshell does not prompt for user input. Commands invoked via .
