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

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL

Computer Network, SQL Scripts, SQL Utility
Previous Post
SQL SERVER – 2005 List All Tables of Database
Next Post
SQL SERVER – 2005 Improvements in TempDB

Related Posts

66 Comments. Leave new

  • Hi Pinal,
    Please suggest me how to export data to Excel or CSV file format using bcp with Column Names

    Reply
  • Hi,
    Is there any way to run .bat files in sql without using xp_cmdshell??
    Please suggest me a way out for running .exe and .bat files inside the stored procedure without using xp_cmdshell.

    Reply
  • hi,
    i need to change my filename in exact location of it in bulk. i want an uniqueness in the filename. i have seven with different names and extension are .docx,.pdf . for example, eye reoport.docx ,i want to rename it as Eye Report.docx. i have totally 24500 records in sequel. kindly tell me if any one find the solution for this.
    Thanks

    Reply
  • hi,
    i want to know is there any command through which we can automatically open sql server account with username and password at particular time suppose at 9.0 clock and execute select query automatically ?
    thanks

    Reply
  • but how to set every 15 minutes batchfile call in sql

    Reply
  • how to call automatic batchfile in every 15minutes using sql

    Reply
  • Hi Dave.. As per my original question about xp_cmdshell.. is there any sql query which will forbid us from using xp_cmdshell command. In my case, previously I was using xp_cmdshell to run batch file, but later requirement changed and I had to execute batch file without using xp_cmdshell. Please suggest if there is a way of doing so.

    Reply
  • Andile Jali
    April 8, 2014 3:05 pm

    xp_cmdshell ‘dtexec.exe /F “C:\Cube\ResRentals.dtsx”‘………runns forever and never stops…..Can anyone help me out here please!!!

    Reply
  • when iam trying to execute the batch from SSMS its saying path not found
    EXEC master..xp_cmdshell ‘D:db\Tools\catal.bat’;
    GO

    The system cannot find the path specified.The path is fine and file exist there.

    Reply
  • I am trying to run a simple ‘Select count(*) from dbo.table’ using SQLCMD and output to a file. But along with the records count I am getting the same query which I executed in the output file. How can I make sure that only the output is written to output file.

    Reply
  • I’ve tried this steps but I’ve got the following error message. I’m using SQL trigger on database table and this is my code for calling my batch file

    DECLARE @Param1 VARCHAR(100);
    DECLARE @Param2 VARCHAR(100);
    DECLARE @CMDSQL VARCHAR(1000);
    SET @Param1 = ‘1234’;
    SET @Param2 = ‘5678’;
    SET @CMDSQL = ‘x:TestMe.bat’ + @Param1 + ‘ ‘ + @Param2;
    EXEC master..xp_CMDShell @CMDSQL;

    com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.

    kindly Help

    Reply
  • Is there a way to call Batch file with Admin priviledge using xp_CMDShell?

    Reply
  • How can I execute a batch file from T-SQL stored procedure, but batch file being on a remote server ?

    Reply
  • Girish Chaudhari
    February 26, 2019 2:35 pm

    Hi Pinal,

    We have scheduled the job for .bat file and it’s running fine. But at some time it stays in executing mode and in that case we do stop/disable the job and enable again.

    Please help.

    Reply
  • Syed Moazzam Mallick
    May 3, 2019 5:03 pm

    Hi Pinal,

    I wanna run standalone batch file with ‘run as administrator’ rights.

    Reply

Leave a Reply

Menu
Exit mobile version