How do I execute a Windows Batch File from SQL?

I have a job set up that in step 1 Deletes the data from a specified table.
In step 2 I would like to have the program run a Bat file I set up. This Bat file is the BCP import.

The bat file works fine if I run it on a windows scheduler, but I figured there has to be a way to run in from with in a SQL job.

I am using SQL Manager 2010, and in there one of the command types is Operating-system command or executable program
Under this option is a box to put in the command.
But I'm unsure of the command I need to use to make this work.

Any help is appreciated.


Who is Participating?
ByteSleuthConnect With a Mentor Commented:
Here are two ways of running batch file using SQL, one without parameters and one with-
1) Running standalone batch file (without passed parameters)
EXEC master..xp_CMDShell 'c:yourfile.bat'

2) Running parameterised batch file
DECLARE @PassedVariable VARCHAR(100)
SET @PassedVariable = '127a'
SET @CMDSQL = 'c:yourfile.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

OSXFreakAuthor Commented:
I think I'm just looking to do the xp_CMDShell as you all suggest.. BUT my question is how do I know if I hae that installed?
I tried to type in EXEC master..xp_CMDShell 'c:qoh.bat' and run it, but it gave me a failure. So next question. what does the "master" mean in this, is this the DB table? Do I need to change this?

Anyhow As I said it failed, so not sure where I went wrong..
I've attached the screen shot of what I'm looking at.. I know it's something simple..

Oh and I did move the QOH.bat to the C drive :)


as you are using sequel server 2010 this sounds like a permissions issue.   xp_CMDShell is an embedded store procedure from Microsoft so it will be included in your installation.  You will just need to grant your user access to the procedure.

Follow the steps in this article if you are unsure on how to grant access to a stored procedure-
OSXFreakAuthor Commented:
ok did the enable part, and when I look at the event manager I see this

Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

I tried to run after doing the enable and failure which led me to event log. Any ideas on the Reconfigure statement?

javaftperConnect With a Mentor Commented:
run the following two statements in order-

sp_configure 'xp_cmdshell', '0'

sp_configure 'xp_cmdshell', '1'

For more info on RECONFIGURE statement see here-
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.