Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: SQL Server 2000 Job Steps Not Executing

    I have a problem with a SQL Server (2000) Agent Job that has 3 steps that don't produce the desired outcome when invoked in total but will produce the desired outcome if the first step is broken up from the last two steps.

    1) The first step uses third party software to generate PDFs to a folder.

    2) The second step executes the following command to ftp the PDFs to a folder and move these PDFs to a backup folder:

    EXEC master..xp_cmdshell 'm: & cd C:\Program Files\foldername1 AutoGeneral_Ftp & dir & FTP -n -s:C:\Progra~1\foldername1\AutoGeneral_FTP\upload_G eneral.ibc www.someorgname.org & move /Y
    \\networkdrive\Archive\General\*.pdf \\networkdrive\Archive\backups\General'

    3) The third step determines if any files remain in the folder that contained the PDFs. If files are still in the folder then the FTP and move step (step 2) did not work and a stored procedure is invoked to send an email to the appropriate administrator

    DECLARE @result int
    EXEC @result = master..xp_cmdshell 'Dir "\\networkdrive\Archive\General\*.pdf" | find /i "file"'
    IF @result = 0
    exec ibc_sp_Email_Report_Failure 'FTP General Report'

    When I invoke this job from Enterprise Manager and view the job history, it says that all steps executed successfully (which I understand may be the case even if the files were not FTPed or moved since the command can still return a code indicating success even though it didn't do what I expected.) In fact, the PDF is generated and written to \\networkdrive\Archive\General\reportname.pdf. Steps 2 and 3 do not do what I expect. The PDFs still remain in the folder.

    But when I start the job from step 2, the files are then moved. So, invoking the entire job does not move the files; invoking the job from step 2 moves the files.

    Simple, you think, it is obvious that you didn't set up your job steps correctly in that step 1 does not go to the next step upon success. But I already checked that. Step 1 goes to the next step upon success.

    Anybody ever come upon this problem? Any suggestions as to what else I can look at?

    Thanks in advance,
    Stedan

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Just for grins, put a pause loop at the beginning of your second step of the job, or something to make it slow down a bit (maybe try to open one of the files?).

    Point being that since the first step invokes a piece of third-party software, chances are there that it may not have released the files before the second step kicks off? A possibility. You could also add some logging logic to your FTP step and see if you can capture any errors at that step.

    In fact, have you had the steps set up to log to an output file? If so, check that file and see if there are any errors or processing entries there that may help pinpoint the issue. If you don't know how to set up that log file, post back and we can help with that. It's set up in the ADVANCED tab of the individual job step.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Nov 2007
    Posts
    4
    Thanks for such a quick response. I did set up the steps to log to an output file as well as add a command that has the job sleep for 10 seconds. The output file indicates that the following file did not exist

    mput M:\Archive\General\*.pdf
    M:\Archive\General\*.pdf:File not found

    That is the only indication that something is wrong, but 1) that folder does exist and 2) the step completes successfully when invoked from step 2. If this was the error I would think that I would get the same error for both invocations. When I look at the log when I invoke from step 2, the mput commands appear like the following :
    mput M:\Archive\General\*.pdf
    quit
    mapnetworkdrive\Archive\General\AreaLocalSCF.pdf
    1 file(s) moved.

    where mapnetworkdrive is the name of the shared drive.

    Why would the network drive not be recognized the first time around and not the second? Any further thoughts?

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I don't think the network drive is the issue here.

    I'm still not convinced that there is anything going on except that the pdf file is actually not THERE when the second step kicks off. Or perhaps more likely, that it IS there, but is inaccessable.

    Try putting some pause logic (a processing loop or whatever, just something to prevent the ftp in step 2 from starting as quickly as it is currently) at the beginning of step two, or at the end of step one, and see if that gives the third-party software enough time to release it's ugly hold on the output file.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Nov 2007
    Posts
    4
    Thanks so much for your input. The command I used to pause was incorrect and I didn't catch it. I used the WAITFOR DELAY '00:00:10' command instead to make the process sleep for 10 seconds. That has appeared to work. Again, thanks for your input.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I have various DTS jobs that go out and FTP get files, I usually use the "Execute process task" to run an ftp script created by an ActiveX script (get file with embedded date for a specific day, script builds .ftp script called via ftp -s:\\sharename\ftpscript.ftp), instead of using xp_cmdshell, never had a problem with it.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by PMASchmed
    I have various DTS jobs that go out and FTP get files, I usually use the "Execute process task" to run an ftp script created by an ActiveX script (get file with embedded date for a specific day, script builds .ftp script called via ftp -s:\\sharename\ftpscript.ftp), instead of using xp_cmdshell, never had a problem with it.
    Same here, actually...though we tend to call a bat file that calls the FTP script file, but essentially it's the same scheme. We've never had a problem either.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •