Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).
share|improve this question
    
1. That /c shouldn't be /C? Not sure, but maybe it's case sensitive. 2. Do you have a PAUSE instruction in your .bat file? 3. Have you tried using CALL to run the batch file? –  Marian Mar 4 '13 at 9:43
    
Is your SQL Agent Service running under a domain account or a local account? Is the KWS2-WEB-SERVER server in the same domain? –  Brian Bentley Mar 6 '13 at 16:06
    
@Marian I dont have a pause instruction in my .bat file. I will try to use 'call'. I don't now exactly what that is, but I will figure it out. –  Nazeem Mar 7 '13 at 21:34
1  
@mugawump The SQL Agent Service is running under a local account. Also, the script runs as Administrator –  Nazeem Mar 7 '13 at 21:35
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thank you for your reply. I the steps you explained are already present in my configuration. (It was unclear in my first-post, my bad) I have a credential named "asAdmin" The identity is as follows: KWS2_SCADA_SERV\Administrator Then I created a proxy named "AsAdminProxy". I selected the appropriate credentials and selected "Operating system (CmdExec)" as the active subsystem. Also, when I look into the "References" tab I see all the job steps, including the one which doesn't run. So i think it is correctly condigured. –  Nazeem Mar 12 '13 at 8:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.