Sql Server JOb Execution problem at the time of FTP
-
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
-
i don't know if this will help you, but this is how I scripted out the ftp object from vb6: i notice i'm using two other parameters: "ftp -n -v -s:"
Set objWS = CreateObject("WScript.Shell")
strShellCmd = "ftp -n -v -s:" & App.Path & "\UNSENT\ftpsendtest.txt"
Set ObjCmd = objWS.Exec(strShellCmd)
DoEvents
FTPResults = ObjCmd.StdOut.ReadAll()
'test for successful alternate sending mode
If InStr(FTPResults, "226 Transfer completed.") Then
nbsp; ' write success to database
end if
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell
I tried your script also, but it wont work. 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
-
I als tried following
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
> ftp: connect :Connection refused
Can anyone tell me whats wrong I am doing? is this because of IP address, not domain name?
-
my file format is as follows with the syntax i used previously; my username and password are on the same line. not much of a difference; googling for "ftp -n -v -s:" gets lots of samples.
open nnn.nnn.nnn.nnn
user "username,password"
binary
put ./TEST1.TXT /SJET3000/ACUDEMO/CPY/TEST1.TXT
get /SJET3000/ACUDEMO/TEST2 ./TEST2.TXT
quit
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - I tried my script on another sql server, it works and created file on ftp server, but it wont transfer the file contents, I tried 'Binary' also, but it wont work. Means it uploaded a blank file with same name.Secondly why it is not working from my server, is this because of ISA server, but If i uplaoded thru directly from CMD prompe using http://ftp.exe or any third part tool like cuteftp both works great. Is there any permission involved, solet meknow how can I check the permission ? I am using user 'SA' which by default is db owner.Let me suggest whats wrong I am doing here?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply