SQL Agent Driving Me Nuts

Question

SQL Agent Driving Me Nuts

davidcb

SSC Enthusiast

Points: 113
More actions
November 29, 2021 at 10:25 am

#3957759

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 🙁

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

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

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 2

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.)

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

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/

davidcb SSC Enthusiast Points: 113 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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)