bat file runs fine when run manually but not in sql job

Posted on 2012-09-13
Medium Priority
Last Modified: 2012-09-14
This is really frustrating .

I have a batch file that runs an exe file provided by a vendor.

Basically the batch file will update 2 text files

if I double click the batch file to run manually   all is well the text files are updated (datetime stamp is updated)

If I run it via a sql job.  I get no errors but the files are not updated.  (looks like nothing happens)

Here is the command I am using in the sql job.  (using master db...did not put anything in run as)

EXEC master..xp_CMDShell '"C:\Program Files (x86)\iViewSystems\iTrak Server\RPLImport.bat"'

I'm guessing a permissions issue???? what account does it use if nothing filled in???
Question by:johnnyg123
  • 2
  • 2
  • 2

Expert Comment

ID: 38396438
You most likely got it right, permissions can get tricky with xp_CMDShell.  Can you right click on the job and 'view history' to see what error you are getting?

Tibor had the best security permissions article that I had read on xp_CMDShell, check it out:

"So, what Windows account is used? If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not "pretend to be somebody else"). But if the login isn't sysadmin, then we need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins!"

Author Comment

ID: 38396478
That's just it.  When I click on view history it says step succeded

I think I ran into something like this awhile back but can't remember

Expert Comment

ID: 38396647
It'll say step succeeded since SQL sent the statement to windows and windows returned an error, so it won't show up in sql server history, that makes sense.

What is your sql server agent and sql server service account user permissions on the machine?  Can you verify them by going to
start - run -services.msc. Finding the user name, then seeing what their permissions are in windows.  Let's start with that.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 27

Expert Comment

ID: 38396713
Open the job properties, then click on Steps and then edit the step. Go to Advanced tab. There is a option Run as user, click on the button and add your Windows domain user. That is the context when you execute the bat file manually. Save the step and job and let it run as scheduled.

Author Comment

ID: 38396807
I think I figured out the problem but not sure how to solve it

I need to specify the starting directory (like in task scheduler)

Is there a way to do this?
LVL 27

Accepted Solution

Zberteoc earned 2000 total points
ID: 38396825
You don't need that. xp_cmdshell executes the command exactly as you pass it to it. It doesn't need a starting folder because the command has the folder explicitly.

If your command works when you execute it manually from SQL Management STudio:

EXEC master..xp_CMDShell 'C:\Program Files (x86)\iViewSystems\iTrak Server\RPLImport.bat'

than is should work from the job as well providing that you executed it as yourself as I posted above.

A thing that I did was to eliminate the " (double quotes) from the command. Try it that way.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard 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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

862 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