How to succesfully run a batch file in an SQL Agent job?

Question

I have a SQL Agent Job which generates a specific report in PDF-file and then copies the PDF to a network directory and then deletes the PDF file in the source directory.

The SQL Jobs consists of 2 steps: 1. Generate the report 2. Copy the report to the network location.

For step 2 I made a bat-file which handles the copying and removal of the pdf file.

The bat-file is as follows:

set source_dir=C:\Reports\Energie\Uur
set dest_dir=\\KWS2-WEB-SERVER\Share\Reports\Uur

C:\Windows\System32\Robocopy.exe %source_dir% %dest_dir% /MOV /Z

However, when I run my the Job, it hangs on the second step. The status just stays on "Executing".

This is the line which I stated in the step (location of the bat-file to execute):

cmd.exe /c "C:\Reports\rapport_uur_copy.bat"

My job-settings are as follows:

Step 1

Type: Operating system (CmdExec) On Success: Go to the next step

On Failure: Quit the job reporting failure

Step 2

Type: Operating system (CmdExec)

On Success: Quit the job reporting success

On Failure: Quit the job reporting failure

Some facts:

• I have read/write permissions on the network directory
• I run the steps as the Administrator-account (the logged in user, default)
• Step 1 succeeds
• I run Windows Server 2008 R2 Standard
• I have SQL Server 2008 R2
• When I run the script from the cmd manually, it works (under Administrator account).

Answer 1

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.

Answer 2

Typically when a job like this hangs it is because the command execution is waiting for a response. Because any command execution run from SQL Server is run as a shell, and you can't see the shell, it can't get a response. If I were you I would try running the bat file manually and see if you are asked for a response. If so correct that part (so that it doesn't ask for a response) and try again.

If running it yourself doesn't help then try running it through xp_cmdshell and see if you get any output back. It may still hang though.

Answer 3

I had the same exact issue. My SQL job would hang and say "Executing..." and never complete.

Add "--trust-model always" to your .bat file.

Basically your .bat file runs when you run it manually because you're the owner. It trusts you. When you run the .bat as a different user, the cmd will actually display a warning and ask you if you trust it. Your job is hanging because the cmd is waiting for your answer. Adding "--trust-model always" will skip this question and allow the .bat to finish.

Trust me. :)

Answer 4

Every step you seperate with \CR \LF in a Script you have written previously only can be executed in the AgentJob if you choose one of those two options:

• Everey seperate line in your bat-script hast to be a seperate step in the AgentJob
• delete all \CR \LF and in your Step of the AgentJob and put a && between your commands

Answer 5

Try using COMSPEC to run the .bat using a .CMD file