?
Solved

How do I execute a Windows Batch File from SQL?

Posted on 2010-08-25
7
Medium Priority
?
281 Views
Last Modified: 2012-05-10
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.

Thanks

Chip
0
Comment
Question by:OSXFreak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 11

Expert Comment

by:Coast-IT
ID: 33521638
0
 
LVL 5

Accepted Solution

by:
ByteSleuth earned 1000 total points
ID: 33521652
0
 
LVL 4

Expert Comment

by:javaftper
ID: 33521667
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)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = '127a'
SET @CMDSQL = 'c:yourfile.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:OSXFreak
ID: 33521996
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 :)

Thanks,

Chip
sql.JPG
0
 
LVL 4

Expert Comment

by:javaftper
ID: 33522072
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-

http://www.mssqltips.com/tip.asp?tip=1020
0
 

Author Comment

by:OSXFreak
ID: 33522393
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?

Chip
0
 
LVL 4

Assisted Solution

by:javaftper
javaftper earned 1000 total points
ID: 33522596
run the following two statements in order-

1.
sp_configure 'xp_cmdshell', '0'

2.
sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO

For more info on RECONFIGURE statement see here-
http://technet.microsoft.com/en-us/library/ms176069.aspx
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question