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

Posted on 2012-09-13
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
    LVL 7

    Expert Comment

    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

    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
    LVL 7

    Expert Comment

    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.
    LVL 26

    Expert Comment

    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

    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 26

    Accepted Solution

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now