Running Programs

Before Microsoft included support for .NET framework, OLE Automation, and COM in SQL Server, administrators ran command-prompt programs and commands using the xp_cmdshell extended stored procedure:

 xp_cmdshell {'command'} [, no_output] 

When xp_cmdshell is executed, a command string is passed to the command shell of the operating system to be executed. Any rows of text that are normally displayed by the command shell are returned by the extended stored procedure as a result set. There is also an option to ignore the output.

The status of the execution is returned as an output parameter of the extended stored procedure. Its value is set to 0 if execution was successful and 1 if execution failed.

Figure 21-1 shows the use of the command-prompt instruction to list files in the Backup folder. This output can be received in a temporary table and further processed in Transact-SQL code.

Figure 21-1: Using xp_cmdshell to run commands and programs

The following batch-copies files from the Backup folder to another drive:

 exec master..xp_cmdshell 'copy e:\w2kPro~l\Micros~l\'                     + 'MSSQL\BACKUP\*.* m:', no_output