Solved

vbscript problems (mainly syntax)

Posted on 2009-07-06
5
801 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:larksys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

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

Regards,

Rob.
0
 
LVL 1

Author Comment

by:larksys
ID: 24793496
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 24799972
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
 
LVL 1

Author Closing Comment

by:larksys
ID: 31600486
Perfect, Thanks
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 24801210
No problem.  Thanks for the grade.

Regards,

Rob.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question