I am trying to automate upload task at midnight. For this I have create file
which generate TAB delimited file, then I create a FTP connection file and
thne execute this ftp script for upload the file, but file is still not
uploading to FTP server. If I Upload file manually, file uploded
succcessfully.

Can any of the guru tell me whats wrong I am doing here?


--------Code Start here

Function Main()

Dim fs
Dim txt

Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get
Dim strLocalFolderName, strFTPServerName, strLoginID
Dim strPassword, strFTPServerFolder

strupload=""

strupload=Strupload & "product_url" & VbTab & "name" & VbTab &
"description" & VbTab & "price" & VbTab & "image_url" & VbTab & "category" &
VbTab &

"offer_id" & VbCrLF

dim strConn
strConn="driver={SQL Server};server=localserver;Persist Security Info =
False;uid=sa;pwd=;database=localserver"

Set conn=CreateObject("ADODB.Connection")
conn.Open strConn

set rs = CreateObject("ADODB.Recordset")

rs.open "SELECT top 10 * FROM items where web_active= 'T'", conn, 1, 3

do while rs.eof<>True
strupload=Strupload & "http://localserver/evp/itemdc.asp?ic=" &
Rs("itemno") & VbTab
strupload=Strupload & Rs("descript") & VbTab
Dim mDescript
mDescript = Rs("webDesc")
mDescript = Replace(mDescript,"<br>","")


strupload=Strupload & mDescript & VbTab
strupload=Strupload & Rs("sellpric") & VbTab
strupload=Strupload & "http://localserver/mmPARKDEVP/Images/" &
Rs("itemno") & ".jpg" & VbTab
strupload=Strupload & "Furniture" & VbTab
strupload=Strupload & Rs("itemno") & VbTab
strupload=Strupload & VbCrLF
rs.movenext
loop

Set fs = CreateObject("Scripting.FileSystemObject")

Set txt = fs.CreateTextFile("C:\" & "upload.txt", TRUE)

txt.Write strFroogle
txt.Close
Set txt = Nothing
Set fs = Nothing

rs.close
set rs=Nothing

conn.Close
set conn=Nothing

'Customize code here to fit your needs
strLocalFolderName = "c:\"
strFTPServerName = IP Address
strLoginID = "administrator"
strPassword = "nopass1"
strFTPServerFolder = ""

'The follow lines of code generate the FTP script file on the fly,
'because the get file name changes every day

strFTPScriptFileName = strLocalFolderName & "uploadftp.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

If (objFSO.FileExists(strFTPScriptFileName)) Then
objFSO.DeleteFile (strFTPScriptFileName)
End If

Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine ("open " & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine ("cd " & strFTPServerFolder)
objMyFile.WriteLine ("ascii")
objMyFile.WriteLine ("lcd " & strLocalFolderName)
objMyFile.WriteLine ("put " & "upload.txt")
objMyFile.WriteLine ("bye")
objMyFile.Close

Set objShell = CreateObject("WScript.Shell")
objShell.Run ("ftp -s:" & chr(34) & strFTPScriptFileName & chr(34))
Set objShell = Nothing
Set objFSO = Nothing
Set objMyFile = Nothing
End Function

------------- Code Ends Here


As far as my script which create a ftP batch file for FTP, if I execute this
on comand prompt it works great, but in job it work infact dont generate any
error. Hoever it create text file and ftp batch file without any problem.

Is there any other way to upload my text file from sql job?

Please help, I am stuck here

Run it from a stored procedure xp_cmdshell 'your path and batFileName'

http://sqlservercode.blogspot.com/



"Ashish @ Armour" wrote:

As you said I have already this also. Here is my batch name myftp.bat
xp_cmdshell 'C:\MyFTP.BAT'

Here is batch file contents

open 192.168.0.1
administrator
deluxe104
cd
ascii
lcd c:\
put c:\data1.txt
bye

It seems it is problem of my batch file contents. Can I run xp_cmdshell in
the job?



"SQL" <SQL (AT) discussions (DOT) microsoft.com> wrote

I als tried this one also, but it is also not working

USE master
GO
EXEC xp_cmdshell 'echo open xxx.xxx.xxx.xxx > c:\ftp.cmd'
EXEC xp_cmdshell 'echo user administrator >> c:\ftp.cmd'
EXEC xp_cmdshell 'echo pass deluxe104 >> c:\ftp.cmd'
EXEC xp_cmdshell 'echo put data.txt >> c:\ftp.cmd'
EXEC xp_cmdshell 'echo bye >> c:\ftp.cmd'
EXEC xp_cmdshell 'ftp -s c:\ftp.cmd -i'
GO

Can anyone tell why it is not working this?

"Ashish @ Armour" <ashishk (AT) armour (DOT) com> wrote

I als tried following, but still no luck

I create a text file akftp.txt, which contain following

open xxx.xxx.xxx.xxx
administrator
deluxe104
put c:\text.txt
bye

then execute it in query analyzer

EXEC xp_cmdshell 'ftp.exe -s:E:\froogle.txt'

got following error


Invalid command.
Invalid command.
Not connected.
open xxx.xxx.xxx.xxx
administrator
deluxe104
put c:\text.txt
bye

Can anyone tell me whats wrong I am doing? is this because of IP address,
not domain name?



"Ashish @ Armour" <ashishk (AT) armour (DOT) com> wrote

I have used this add-on, a DTS FTP task (located here:
http://www.sqldts.com/default.aspx?302 ), and it has worked for me
before. You could give it a try and work it into your automation,
instead of the batch file if you wanted to try something different.


Ashish @ Armour wrote:

Coincidentally, I am working with FTP today. This works for me.

The FTP command line is:
ftp -s:\YourDownloadDirectory\YourFTPCommandFile.txt 000.111.222.333

YourFTPCommandFile.txt looks like this:

YourUserID
YourPassword
cd /RemoteMachineDirectory
lcd D:\YourDownloadDirectory
get YourDownloadFileName
quit

It's a get, not a put, but I hope this helps.

Payson

Ashish @ Armour wrote:

Hi

You may wish to try using the -n flag and use the user command to pass the
username and password.

user UserName [Password] [Account]

Also the -v and -i flags may be useful.

John

"Ashish @ Armour" wrote:

On Mon, 10 Oct 2005 19:21:20 +0530, Ashish @ Armour wrote:

If you're not averse to using a different tool for ftp besides the
brain-dead MS ftp.exe, I can recommend curl or wget. Each of them is
fantastically flexible; curl is slightly simpler.

I tried my script on another sql server, it created file on ftp server, but
it wont transfer the file contents, I tried 'Binary' also, but it wont
work.

Secondly why it is not working from server, is this because of ISA server,
but IF i uplaoded thru directly from CMD prompe using ftp.exe or any third
part tool like cuteftp both works great.

Let me suggest whats wrong I am doing here?

"Ashish @ Armour" <ashishk (AT) armour (DOT) com> wrote