vbscript problems (mainly syntax)

I have a script that pulls data from an old
sql2000 database and transfers (viaFTP) that data to an AS400 database and also to an SQL2005 database.  Prior to transferring the data, old data in the AS400 database has to be deleted. I finally worked that out. Now I want to check the results of the FTP log file to see if the FTP was successful. If not, FTP again until it's successful.

'Option Explicit
 
Dim Prompt1,Title1,WSHShell,result
Prompt1 = "Enter a date to re-run call data for (mm/dd/yyyy) -"
Title1 = "Telemate Rerun Call Data"
 
Set WSHShell = WScript.CreateObject("WScript.Shell")
 
result = InputBox(Prompt1,Title1,"", 100, 100)
 
If result = "" Then
	WScript.Quit()
Else 
	
	
	Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get
	Dim strLocalFolderName, strFTPServerName, strLoginID
	Dim strPassword, strFTPServerFolder, strToday, strCopyScriptFileName
	Dim strLogFile, strFile2Put, strCopyLogFile,strLocalCopyFolderName
	Dim strSource
	Dim strLiveFTPServerName, strLiveLoginID
	Dim strLivePassword, strLiveFTPServerFolder
	Dim  strLiveFTPScriptFileName,strLiveFile2Put,strLiveLogFile
	
	Function pd(n, totalDigits) 
		If totalDigits > Len(n) Then 
			pd = String(totalDigits-Len(n),"0") & n 
		Else 
			pd = n 
		End If 
	End Function 
	
	strToday = pd(Right(Year(DateAdd("d",0,result)),2),2) & pd(Month(DateAdd("d",0,result)),2) & pd(Day(DateAdd("d",0,result)),2)
	
	' Log file
	strLogFile="c:\rawdata\taxsearch\telemate\TM_Daily_FTP_AS400_SQL.log"
	strLiveLogFile="c:\rawdata\taxsearch\telemate\TM_Daily_FTP_Live_SQL.log"
	
	' Copy Log file
	strCopyLogFile="c:\rawdata\taxsearch\telemate\TM_Daily_Copy_AS400_SQL.log"
	
	
	strLocalFolderName = "c:\rawdata\taxsearch\telemate"
	strLocalCopyFolderName = "c:\rawdata\taxsearch\Telemate"
	strFTPServerName = "192.168.1.50"
	strLiveFTPServerName = "24.28.23.26"
	strLoginID = "quser"
	strLiveLoginID = "anonymous"
	strPassword = "quser"
	strlivePassword = "dudej@dude.net"
	strFTPServerFolder = "tsi"
	strLiveFTPServerFolder = "\telemate\tulsa"
	strFile2Get = "CD" & strToday & ".fil"
	strFile2Put = "TMdata"
	strLiveFile2Put = "TMdata.txt"
	strSource = "tul"
	
	'--------------------------------------------------------
	
	'  The following code deletes data from the AS400 database
	
	Const adOpenStatic = 3
	Const adLockOptimistic = 3
	Dim strAS400UpdateString,objConnectionAS400,objRecordSetAS400
	Dim strSqlStringAS400
	dim cnnstr
	
	strSqlStringAS400 = "DELETE FROM TSI.TMDATA " & _
	"WHERE tmdate = '" &strToday& "' and tmsource = 'tul'"
	
	Set objConnectionAS400 = CreateObject("ADODB.Connection")
	Set objRecordSetAS400 = CreateObject("ADODB.Recordset")
	cnnstr = "DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=192.168.1.50;USERID=qUser;PWD=quser"
	
	objConnectionAS400.Open = cnnstr
	set objRecordSetAS400.ActiveConnection = objConnectionAS400
	objConnectionAS400.Execute(strSQLStringAS400)
	
	'objRecordSetAS400.close
	Set objConnectionAS400 = Nothing
	
	
	
	'--------------------------------------------------------
	
	'  The following code selects todays call data from sql database
	
	Dim strSqlUpdateString,objConnection2,objRecordSet2
	Dim strSqlString,objConnection ,objRecordSet,strFTPDataFileName
	
	strSqlString = "SELECT * FROM CallData " & _
	"INNER JOIN History ON CallData.ID_VirtualResource = History.ID_CommResource AND CallData.SessionDateTime BETWEEN History.StartDate AND History.EndDate " & _
	"INNER JOIN CommResource DISA ON CallData.ID_VirtualResource = DISA.ID_CommResource " & _
	"INNER JOIN CommResource EXTN ON CallData.ID_CommResource = EXTN.ID_CommResource " & _
	"INNER JOIN Owner ON History.ID_Owner = Owner.ID_Owner " & _
	"INNER JOIN Department ON History.ID_Department = Department.ID_Department " & _
	"INNER JOIN Division ON History.ID_Division = Division.ID_Division " & _
	"INNER JOIN Company ON History.ID_Company = Company.ID_Company " & _
	"WHERE CONVERT(varchar, SessionDateTime, 101) = CONVERT(varchar, '" &result& "', 101)"
	
	Set objConnection2 = CreateObject("ADODB.Connection")
	Set objRecordSet2 = CreateObject("ADODB.Recordset")
	
	objConnection2.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=tm4G;Initial Catalog=cats;Data Source=thelmaspc\telemate"
	
	
	Set objConnection = CreateObject("ADODB.Connection")
	Set objRecordSet = CreateObject("ADODB.Recordset")
	
	objConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=tm4G;Initial Catalog=cats;Data Source=thelmaspc\telemate"
	Set objRecordSet=objConnection.Execute(strSQLString)
	
	If objRecordSet.eof Then
		'MsgBox "no records"
		
		WScript.Echo " No records selected for " & result
		
		strFTPDataFileName = "c:\rawdata\taxsearch\telemate\CDToday.txt"
		Set objFSO = CreateObject("Scripting.FileSystemObject")
		
		If (objFSO.FileExists(strFTPDataFileName)) Then   
			objFSO.DeleteFile (strFTPDataFileName)
		End If
		
		
		Set objMyFile = objFSO.CreateTextFile(strFTPDataFileName, True)
	Else
			
		objRecordSet.MoveFirst
		
		strFTPDataFileName = "c:\rawdata\taxsearch\telemate\CDToday.txt"
		Set objFSO = CreateObject("Scripting.FileSystemObject")
		
		If (objFSO.FileExists(strFTPDataFileName)) Then   
			objFSO.DeleteFile (strFTPDataFileName)
		End If
			
		Set objMyFile = objFSO.CreateTextFile(strFTPDataFileName, True)
		
		Do Until objRecordSet.eof
			
			objMyFile.Write (strSource & _
			Right(Space(8) & objRecordSet("id_calldata"),8) & _
			Left(objRecordSet("areacode") & Space(3),3) & _
			strToday & _
			Right( Space(3) & objRecordSet("id_virtualresource"),3) & _
			Right( Space(2) & objRecordSet("id_trunk"),2) & _
			Left(objRecordSet("id_calltype") & Space(2),2) & _
			Left(objRecordSet("shifttime") & Space(5),5) & _
			Left(objRecordSet("dialednumber") & Space(30),30) & _
			Left(objRecordSet("direction") & Space(2),2) & _
			Right(Space(5) & objRecordSet("durationseconds"),5) & vbCrLf)
			
			strSqlUpdateString = "Update calldata set MeterPulses = 1 WHERE ID_CallData =" & objRecordSet("id_calldata")
			'msgbox strSqlUpdateString
 
			Set objRecordSet2=objConnection2.Execute(strSQLUpdateString)	
			objRecordSet.MoveNext
		Loop
		
		objMyFile.Close
		Set objFSO = Nothing
		Set objMyFile = Nothing
		
		
		
		objRecordSet.close
		Set objConnection = Nothing
		Set objConnection2 = Nothing
		
		' ----------------------------------
		
		'The follow lines of code generate the FTP script file on the fly,
		'because the get file name changes every day
		
		
		
		'------------------------
		' FTP to AS400
		
		strFTPScriptFileName = "c:\rawdata\taxsearch\telemate\FTPScriptAS400.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 " & strLocalCopyFolderName)
		objMyFile.WriteLine ("append CDtoday.txt " & strFile2Put)
		objMyFile.WriteLine ("bye")
		
		objMyFile.Close
		Set objFSO = Nothing
		Set objMyFile = Nothing
		
		
		
		'------------------------
		' FTP to AS400 SQL server
		'The following code executes the FTP script. It creates a Shell
		'object and run FTP program on top of it.
		
			Const OpenAsDefault = -2
			Const FailIfNotExist = 0
			Const ForReading = 1
			Const ForWriting = 2		
			Dim strFTPCmd		
			Dim FTPupload
			Dim FTPResults			
			FTPupload = False
 
		
		Do Until FTPupload = True
 
			Set objShell = WScript.CreateObject( "WScript.Shell" )
		
			
			strFTPCmd="cmd /c %SystemRoot%\System32\ftp -s:" & Chr(34) & strFTPScriptFileName & Chr(34) & " > """ & strLogFile & """"
		
			dim filesys
			Set filesys = CreateObject("Scripting.FileSystemObject") 
			If filesys.FileExists("'" & strLogFile & "'") Then
				filesys.DeleteFile ("'" & strLogFile & "'")
			End If
 
			Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
				
			objShell.Run strFTPCmd, 1, True
			WScript.Sleep 1000
			
			'Check results of transfer.
			if filesys.FileExists(strLogFile) then			
				Set fFTPResults = oFTPScriptFSO.OpenTextFile(strLogFile, ForReading, _
				FailIfNotExist, OpenAsDefault)
				sResults = fFTPResults.ReadAll
				If InStr(sResults, "250 File transfer completed successfully.") > 0 Then
					FTPUpload = True
				ElseIf InStr(sResults, "226 Transfer complete") > 0 Then
					FTPUpload = True
				ElseIf InStr(sResults, "Not connected") > 0 Then
					FTPUpload = "Error: File Not Found"
				ElseIf InStr(sResults, "Invalid command") > 0 Then
					FTPUpload = "Error: Login Failed."
				Else
					FTPUpload = "Error: Unknown."
				End If
			End if
 
			fFTPResults.Close		
		Loop
		
		Set objShell = Nothing
		
		'------------------------
		' FTP to live SQL server
		
		FTPupload = False		
 
		Do Until FTPupload = True
		
			strLiveFTPScriptFileName = "c:\rawdata\taxsearch\telemate\FTPScriptLiveSQL.txt"
			Set objFSO = CreateObject("Scripting.FileSystemObject")
		
			If (objFSO.FileExists(strLiveFTPScriptFileName)) Then
				objFSO.DeleteFile (strLiveFTPScriptFileName)
			End If
			
			Set objMyFile = objFSO.CreateTextFile(strLiveFTPScriptFileName, True)
			objMyFile.WriteLine ("open " & strLiveFTPServerName)
			objMyFile.WriteLine (strLiveLoginID)
			objMyFile.WriteLine (strLivePassword)
			objMyFile.WriteLine ("cd " & strLiveFTPServerFolder)
			objMyFile.WriteLine ("ascii")
			objMyFile.WriteLine ("lcd " & strLocalCopyFolderName)
			objMyFile.WriteLine ("append CDtoday.txt " & strLiveFile2Put)
			objMyFile.WriteLine ("bye")
			objMyFile.Close
			Set objFSO = Nothing
			Set objMyFile = Nothing
		
		'The following code executes the FTP script. It creates a Shell
		'object and run FTP program on top of it.
			WScript.Sleep 1000
			Set objShell = WScript.CreateObject( "WScript.Shell" )
			strFTPCmd="cmd /c %SystemRoot%\System32\ftp -s:" & Chr(34) & strLiveFTPScriptFileName & Chr(34) & " > """ & strLiveLogFile & """"
			Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
			objShell.Run strFTPCmd, 1, True
			Set objShell = Nothing
		'Check results of transfer.
			Set fFTPResults = oFTPScriptFSO.OpenTextFile(strLiveLogFile, ForReading, _
			FailIfNotExist, OpenAsDefault)
			sResults = fFTPResults.ReadAll
			fFTPResults.Close
			
			'oFTPScriptFSO.DeleteFile(sFTPTempFile)
			'oFTPScriptFSO.DeleteFile (sFTPResults)
			
			If InStr(sResults, "250 File transfer completed successfully.") > 0 Then
				FTPUpload = True
			ElseIf InStr(sResults, "226 Transfer complete") > 0 Then
				FTPUpload = True
			ElseIf InStr(sResults, "Not connected") > 0 Then
				FTPUpload = "Error: File Not Found"
			ElseIf InStr(sResults, "Invalid command") > 0 Then
				FTPUpload = "Error: Login Failed."
			Else
				FTPUpload = "Error: Unknown."
			End If
		Loop
	End If
End If

Open in new window

LVL 1
larksysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi, that loop around the FTP upload looks like it should work....which part of the script is not working?

Regards,

Rob.
0
larksysAuthor Commented:
Line 299 gives me a "read past end" error when the FTP fails.  There is nothing in the FTP log file. I used to get a log of errors until I tried looping through it until the FTP was successful.
0
RobSampsonCommented:
Ah, OK, you should be able to change that line from this:
                  sResults = fFTPResults.ReadAll

to this
                  If fFTPResults.AtEndOfStream = True Then
                        sResults = "Error: Unknown."
                  Else
                        sResults = fFTPResults.ReadAll
                  End If


so that you check if the file is empty.

Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
larksysAuthor Commented:
Perfect, Thanks
0
RobSampsonCommented:
No problem.  Thanks for the grade.

Regards,

Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.