SQL Agent Driving Me Nuts

  • Been given a new server running Win 2019 & SQL 2019 unable to run bat jobs with SQL Agent

    So to try & simplify & understand what is happening have a simple bat file that is called test.bat,  contains the following

    @echo off

    echo Test Test Test

    pause

    SQL agent has full access rights to partition even temporarily granted Admin rights to account.

    When running agent task, task runs but gives no errors.

    Syntax within job properties c:\windows\system32\cmd.exe /c "E:\xxxxxxx\Test.bat"

    Tried running a very simple agent job to call notepad.exe  also alas no success.

    Any pointers appreciated  as this is driving me nuts 🙁

     

  • If you're running the .bat file as a Operating system (CmdExec) step, have you tried running it as a SQL Server agent proxy using credentials for a user that has the correct Windows permissions?  If I remember rightly, it's not the SQL Server permissions that are important here, but the OS permissions.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Also, you do not want to be running anything which requires user interaction from the agent. eg pause, notepad etc.

    ie The agent is running as its own process on the server.

    You can also direct output to a file by specifying an output_file_name with sp_add_jobstep. (I think this is on the advanced job tab in SSMS.)

  • davidcb wrote:

    Been given a new server running Win 2019 & SQL 2019 unable to run bat jobs with SQL Agent

    So to try & simplify & understand what is happening have a simple bat file that is called test.bat,  contains the following

    @echo off echo Test Test Test pause

    SQL agent has full access rights to partition even temporarily granted Admin rights to account.

    When running agent task, task runs but gives no errors.

    Syntax within job properties c:\windows\system32\cmd.exe /c "E:\xxxxxxx\Test.bat"

    Tried running a very simple agent job to call notepad.exe  also alas no success.

    Any pointers appreciated  as this is driving me nuts 🙁

    Are you getting an error, or is it not showing you the output.  In your example, ECHO Test test test, you won't get any output unless you direct the output to a file.

    If its an error, what is the specific error?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Thanks for your speedy replies contacted sys administrator numerous group policies had been applied.

    Server now same as other SQL instances no issues running various batch files & agent jobs similar to other

    instances.

     

    Thanks again

     

  • davidcb wrote:

    Thanks for your speedy replies contacted sys administrator numerous group policies had been applied.

    Server now same as other SQL instances no issues running various batch files & agent jobs similar to other

    instances.

    Thanks again

    Did the administrators explain what was different that caused the problems to begin with other than just "numerous group policies"?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply