Professional Web Applications Themes

Error running ftp script from xp_cmdshell - Microsoft SQL / MS SQL Server

Hi All, My programmers have asked for my help in troubleshooting an ftp issue we're having when calling an FTP command from a DTS but I'm stumped. I'm not a programmer myself so hopefully I provide you with enough info. Basically, we have a DTS that runs nightly - goes out to a client FTP site, downloads some files and imports them into the database. For some reason, it just stopped working. We're running the ftp command with the -s switch which allows us to call an ftp script from a text file. Here is the code that should do ...

  1. #1

    Default Error running ftp script from xp_cmdshell

    Hi All,
    My programmers have asked for my help in troubleshooting an ftp issue
    we're having when calling an FTP command from a DTS but I'm stumped.

    I'm not a programmer myself so hopefully I provide you with enough
    info. Basically, we have a DTS that runs nightly - goes out to a
    client FTP site, downloads some files and imports them into the
    database. For some reason, it just stopped working.

    We're running the ftp command with the -s switch which allows us to
    call an ftp script from a text file. Here is the code that should do
    this:

    DECLARE bcpCommand varchar(2000) SET bcpCommand = 'ftp
    -s:K:\clients\husa\data\dev\cargo\ftp_get.txt' EXEC
    master..xp_cmdshell bcpCommand

    The error I get is the following:
    Error opening script file K:\clients\husa\data\dev\cargo\ftp_get.txt.

    If I open a command prompt, and type the part that appears to be
    failing:
    ftp -s:K:\clients\husa\data\dev\cargo\ftp_get.txt

    There are no problems opening/executing the FTP script and it goes out
    and does everything it's supposed to.

    It seems like it could be permission related so I've attempted to copy
    the ftp script locally and call to the local file but I have the exact
    same results. I'm able to run other commands using the xp_cmdshell
    such as 'dir' with no problems. Please let me know if you have any
    further questions and thanks in advance for your help!

    Heather Morris Masterson
    Heather Guest

  2. #2

    Default Re: Error running ftp script from xp_cmdshell

    Is K a network drive when you're logged on? If so, try using a UNC path
    that your agent has rights to.

    Ray at work

    "Heather Masterson" <com> wrote in message
    news:google.com...
     
     


    Ray Guest

  3. #3

    Default Re: Error running ftp script from xp_cmdshell

    Hiya Ray,
    Thanks very much for responding. I'd already tried moving the script to
    my local drive then modified the code to look for the script locally but
    it still gives me the exact same error so I don't think it has to do
    with the drive being mapped. I've also experimented (while calling the
    script file on my local drive) with file permissions and given
    'everyone' full rights to the directory/file. It's driving me batty!

    My programmers informed later today that this function is working fine
    on our 'live' server - it's just not working on the development server
    and they needed to make some changes to the DTS and test it before
    deploying to the live server... I've compared the code on dev and live
    and they are identical (with the exception of the paths) which still
    leads me to believe it's permission related.

    Thanks again for taking the time to respond.
    Heather Masterson





    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Heather Guest

  4. #4

    Default Re: Error running ftp script from xp_cmdshell

    Hi Heather,

    First, my disclaimer, I'm a SQL Server rookie.

    I executed this:
    DECLARE bcpCommand varchar(2000)
    SET bcpCommand = 'ftp -n -s:D:\test.ftp' EXEC
    master..xp_cmdshell bcpCommand

    Where the contents of D:\test.ftp on my SQL Server are:

    open server
    user domain\username
    thepassword
    type ascii
    lcd D:\deleteMe
    put test.txt
    close
    quit

    All ran fine for me. The one thing I did differently was to use the -n
    switch for ftp to supress the login, but I don't think that's your issue,
    since your error is on the client side, not the remote server.

    If it were me trying to figure it out, I'd probably add these couple of
    lines first to see if the server can access your drive, your full path, and
    then to see if it has read access to your ftp file:

    DECLARE bcpCommand varchar(2000)
    SET bcpCommand =
    'echo Dirring root of K & dir K: & echo. & echo. & echo Now dirring full
    path & dir K:\clients\husa\data\dev\cargo & echo. & echo. & echo Now trying
    to read file & type K:\clients\husa\data\dev\cargo\ftp_get.txt & echo. &
    echo. & ftp -s:K:\clients\husa\data\dev\cargo\ftp_get.txt'
    EXEC master..xp_cmdshell bcpCommand

    Ray at home

    --
    Will trade ASP help for SQL Server help


    "Heather Masterson" <com> wrote in message
    news:phx.gbl... 


    Ray Guest

Similar Threads

  1. "Script running SLOW!!" error messege
    By Brandy in forum Macromedia Flash Player
    Replies: 3
    Last Post: January 17th, 05:38 PM
  2. Running ASP Script
    By kkks mmss in forum ASP Database
    Replies: 4
    Last Post: July 8th, 08:16 AM
  3. INed error when running script via cron
    By futsnfup in forum AIX
    Replies: 0
    Last Post: September 23rd, 05:15 PM
  4. running SSH through a PHP script
    By James Martin in forum PHP Development
    Replies: 1
    Last Post: July 18th, 03:45 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139