SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure.
The questions are
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?

I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute shell/system command, which includes batch file.

1) Example of running system command using xp_cmdshell is SQL SERVER – Script to find SQL Server on Network

EXEC master..xp_CMDShell 'ISQL -L'

2) Example of running batch file using T-SQL
i) Running standalone batch file (without passed parameters)
EXEC master..xp_CMDShell 'c:findword.bat'

ii) Running parameterized batch file

DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL

Book Online has additional examples of xp_cmdshell
A. Returning a list of executable files
B. Using Windows net commands
C. Returning no output
D. Using return status
E. Writing variable contents to a file
F. Capturing the result of a command to a file