Solved Error handling in vbscript and Return code to batch file

July 3, 2013 at 08:44:21
Specs: Windows Server 2003
Hi Friends,
I need your help in below script.
I want to do error handling in the below vb script. If any error occurs it should exit with number and it will pass to Batch script because im caling the below VB script from batch file.

The VBScript : -- Count.vbs

Const SMTPServer = "mail.XXX.com"
Const Recipient = "XXX"
Const From = "no_reply@XXX.com"
Const Subject = "Facets Daily Batch ID and counts"
Const Message = "Listing of today's claim batch ID's and count is attached. Total Count: "
Const fileName = "Batch_Count.txt"
ConnectString = "Driver={SQL Server};Server=FATESTRPT\FATEST;Database=fi4_xc_test;user id=XXXXX;password=XXXXXX;"
Const FileQuery = "Exec rpt_XC_BUF_SUM"
Const SummaryQuery = "select count(*) from fi4_xc_test.dbo.XC_BUF where TBL_ORDER_ID='C'"


Set fso = CreateObject("Scripting.FileSystemObject")
Set com = CreateObject("ADODB.Command")
com.ActiveConnection = ConnectString
com.CommandText = FileQuery
Set rs = com.Execute
rs.MoveFirst : Set outFile = fso.OpenTextFile(fileName, 2, True)
outFile.WriteLine(Date)
Do Until rs.EoF
For Each f In rs.Fields
line = line & " " & f
Next 'f
outFile.WriteLine Mid(line, 2)
rs.MoveNext : line = ""
Loop
outFile.Close

com.CommandText = SummaryQuery
Set rs = com.Execute
rs.MoveFirst
body = Message & rs(0)

GenericSendmail SMTPserver, From, Recipient, Subject, body, fso.GetAbsolutePathName(fileName)
WScript.Quit

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message, Attachment)
Set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Subject = Subject
msg.TextBody = Message
msg.AddAttachment Attachment
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub

Batch file - Calling the above script.

Cscript Count.vbs

if errorlevel 1 set errlev=42

exit /b %errlev%

edited by moderator: Removed sensitive information -Razor2.3


See More: Error handling in vbscript and Return code to batch file

Report •


#1
July 3, 2013 at 10:45:16
✔ Best Answer
I usually leave error checking as an exercise for the reader, but this would be the bare bones version to get you started:
Const SMTPServer = "mail.XXX.com"
Const Recipient = "XXX"
Const From = "no_reply@XXX.com"
Const Subject = "Facets Daily Batch ID and counts"
Const Message = "Listing of today's claim batch ID's and count is attached. Total Count: "
Const fileName = "Batch_Count.txt"
ConnectString = "Driver={SQL Server};Server=FATESTRPT\FATEST;Database=fi4_xc_test;user id=XXXXX;password=XXXXXX;"
Const FileQuery = "Exec rpt_XC_BUF_SUM"
Const SummaryQuery = "select count(*) from fi4_xc_test.dbo.XC_BUF where TBL_ORDER_ID='C'"


Set fso = CreateObject("Scripting.FileSystemObject")
Set com = CreateObject("ADODB.Command")
On Error Resume Next
com.ActiveConnection = ConnectString
If Err.Number Then Abort(Err.Number) 'Database connect failed
com.CommandText = FileQuery
Set rs = com.Execute
If Err.Number Then Abort(Err.Number) 'Database call failed
rs.MoveFirst : Set outFile = fso.OpenTextFile(fileName, 2, True)
outFile.WriteLine(Date)
Do Until rs.EoF
  For Each f In rs.Fields
    line = line & " " & f
  Next 'f
  outFile.WriteLine Mid(line, 2)
  rs.MoveNext : line = ""
Loop
outFile.Close
If Err.Number Then Abort(Err.Number) 'File Open/Write failed


com.CommandText = SummaryQuery
Set rs = com.Execute
If Err.Number Then Abort(Err.Number) '2nd database call failed
rs.MoveFirst
body = Message & rs(0)

GenericSendmail SMTPserver, From, Recipient, Subject, body, fso.GetAbsolutePathName(fileName)
WScript.Quit

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message, Attachment)
  Set msg = WScript.CreateObject("CDO.Message")
  If Err.Number Then Abort(Err.Number) 'Problem with CDO.Message
  msg.From = From
  msg.To = Recipient
  msg.Subject = Subject
  msg.TextBody = Message
  msg.AddAttachment Attachment
  msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
  msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  msg.Configuration.Fields.Update
  If Err.Number Then Abort(Err.Number) 'Mail config problem
  msg.Send
  If Err.Number Then Abort(Err.Number) 'Mail sending failed
End Sub

Sub Abort (errNum)
  On Error Resume Next
  WScript.StdErr.WriteLine "Error #" & errNum
  WScript.Quit errNum
End Sub

Batch file:

Cscript Count.vbs || set errlev=42

How To Ask Questions The Smart Way


Report •
Related Solutions


Ask Question