SQL agent job failed

Question

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 ?

Answer 1

Hi @Anne ,
Could you please check if the login account for SQL Server Agent have enough permission to the share file?
You can find the login account for the SQL Server Agent in the SQL Server Configuration Manager -> SQL Server Services -> double-click SQL Server Agent(instancename)-> Log on tab.
And you can add permissions for the account in folder properties->Security tab.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

I tried to add the SQL server account (it is a domain account) to local administrator group, that did not work.

I found I have to specifically to add and grant SQL server service account permission to the folder D:\Program Files\Microsoft SQL Server\Client SDK. then it worked,

Strange

Answer 3

I certainly no longer add the service account to administrator group. That is just for testing to verify what was wrong.