Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: SQL Server agent job - SQL Server 7

    Can anyone tell me why the following command(step) will not run in a SQL Server agent job as Type: Operating System Command (cmdexec). It runs fine from the command line just fine. I also tried it with a redirect (<) instead of the -s: switch. Job indicates a successful run in "view job history", but the file is never present on the target host (from command line exec it gets there). It runs fine from the command line, and user running the job on SQL is an administrator.

    command:
    ftp -i -n -v -s:\APPS\MSSQL7\BACKUP\FTPPut.ftp ddmph18

    contents of FTPPut.ftp:
    user logger xxxxxxxx
    ascii
    cd /jobsdir/logger
    lcd \APPS\MSSQL7\BACKUP
    put Runbackup.log URMPWDA13.Runbackup.log.info
    bye

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    My guess is that the SQL Server Agent service is using the system account, and doesn't have access to a network resource. The SYSTEM account only have access to local resources.

    Bill

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by cascred
    My guess is that the SQL Server Agent service is using the system account, and doesn't have access to a network resource. The SYSTEM account only have access to local resources.

    Bill
    Bill, this is true.

  4. #4
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Are you sure it is running FTP? If not, it maybe a path issue. You may want to use full pathname for both ftp and your file. Secondly, the lcd inside is set with relative path. Assuming the ftp is started successfully, the system will try to set from the current path (system directory) and it may not exist.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by skrishnamurthy
    Are you sure it is running FTP? If not, it maybe a path issue. You may want to use full pathname for both ftp and your file. Secondly, the lcd inside is set with relative path. Assuming the ftp is started successfully, the system will try to set from the current path (system directory) and it may not exist.
    It runs fine from the command line, the problem lies between SQL Server Agent and the command shell, I think Bill nailed it. I will put the full path before the ftp command to rule this out.

    ruled out, Bill is the key holder at the moment.

    Thanks,
    Paul
    Last edited by PMASchmed; 10-05-05 at 17:25.

  6. #6
    Join Date
    Sep 2005
    Posts
    28
    I am running SQL 2000 on local system account and able to schedule a job to ftp the file to the remote location. My understanding is that you are not connecting to the network but running a local job and it happens to be FTP utility. Hence, the local system account is not an issue. Here is what I ran and saw it worked.

    Job scheduler

    xp_cmdshell 'ftp -i -n -v -s:d:\testdir\myftpcmds.dat myremotesys >> d:\ftpcmds.log'

    The myftpcmds.dat contains:

    USER user passwd
    cd /usr/backup/files
    lcd d:\backup\files\log
    bin
    put file.log file.log
    bye


    It ran successfully without any issues. You may want to redirect your ftp output and go from there. My suspicion is the LCD command. The default path is c:\winnt\system32 assuming that is where your OS is. I hope your app is present in the same drive.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by skrishnamurthy
    I am running SQL 2000 on local system account and able to schedule a job to ftp the file to the remote location. My understanding is that you are not connecting to the network but running a local job and it happens to be FTP utility. Hence, the local system account is not an issue. Here is what I ran and saw it worked.

    Job scheduler

    xp_cmdshell 'ftp -i -n -v -s:d:\testdir\myftpcmds.dat myremotesys >> d:\ftpcmds.log'

    The myftpcmds.dat contains:

    USER user passwd
    cd /usr/backup/files
    lcd d:\backup\files\log
    bin
    put file.log file.log
    bye


    It ran successfully without any issues. You may want to redirect your ftp output and go from there. My suspicion is the LCD command. The default path is c:\winnt\system32 assuming that is where your OS is. I hope your app is present in the same drive.

    I'll give it a try, I did not preface the command with xp_cmdshell, I ran it in it's native format, perhaps that is the issue.

    Anyway, I just created a scheduler job, and that solved the problem. I'll give the xp_cmdshell a try.

    Thanks much.

  8. #8
    Join Date
    Sep 2005
    Posts
    28

    Wink

    I am reasonably sure that the problem is that you did not use xp_cmdshell. If you do not then SQL Server is treating the command as SQL command. If you plan to schedule through SQL Server, please ensure that SQL Server Agent service is running; otherwise, the jobs will not run.

Posting Permissions

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