Solved

vbscript problems (mainly syntax)

Posted on 2009-07-06
5
784 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now