SQL agent job failed

Question

SQL agent job failed

sqlfriend

SSC Guru

Points: 52769
More actions
December 5, 2020 at 4:22 pm

#3817313
I recently upgrade and migrate a SQL server 2017 to 2019 new instance.
Moved the job to the new server 2019 too. But when running the SQL agent job, I got error.
The process could not be created for step 4 of job 0xC2024C5F74A0AA4996BCCDD1658A2602 (reason: The system cannot find the file specified). The step failed.
this job step, and it is operating sytem (CMDEXEc) type, below is the command entered in job step
sqlcmd -b -S smyserver -d mydb -E -Q "set nocount on;select StudentNumber AS student_number , GuardianContactId AS guardian_contact_id from dbo.mytable " -o "\\anotherrfileserver\data\import\myfile.dat" -W -w 700 -h-1 -s","
I run this directly by opening a cmd prompt, it runs OK. but the job runs failed.
The same job same service account runs on old 2017 server, runs fine too.
What could be the problem ?

Thanks
- This topic was modified 3 years, 5 months ago by sqlfriend.
- This topic was modified 3 years, 5 months ago by sqlfriend.

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

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

I'm thinking that the services accounts for the new server have no privs on the target directory. Fix that and you should be golden.

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks,

But the new server uses the same service account as the old server. and the service account does have access to the network drive.

And the same account runs the job successfully on the old server.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

In that case, I don't have any good guesses. The error message is pretty clear about it not being able to find the file. I'm still thinking it's a privs issue or the directory doesn't actually exist on the target machine.

Another issue might have to do with TLS. If the new machine is running TLS 1.2 (and it SHOULD be for security reasons) and the old machine is still running TLS 1.0 or 1.1, you're going to need to update that other machine.

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

rr_littletons SSC Veteran Points: 283 More actions · Answer 4

Make sure the account running your sql server agent service has correct permission to the share (R\W) and possible file.

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 5

The agent job runs under the context of the owner of the agent job - or the service account if the owner is a sysadmin. Verify the owner of the agent job is correct and the same as on the 2017 instance.

The other possibility is that the agent job was setup to use a proxy account on 2017 and not setup to use that same proxy account on 2019.

I would also check the previous steps...it appears this step is attempting to output a file to the specified location, but the error is stating that the file is not found. If this were a permissions issue to the location I would expect an access denied - not file not found which leads me to suspect a different step (the step that imports the created file?) is the one failing.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

Thanks,

I found out the errors is actually cannot find sqlcmd, please see my top original post.

SQLcmd is in path, but the sql service account which is a domain account got access denied to folder d:\Program Files\Microsoft SQL Server\Client SDK.

I tried earlier to add the sql service account to local admin group, that did not work.

I found I have to specifically grant permission to the folder d:\Program Files\Microsoft SQL Server\Client SDK. then it worked.

This makes me think, I did unistall and reinstall on this computer, when doing uninstall, it seems leave client SDK untouched.

Reinstall may not grant the permission again?

Thanks