HomeTom - CS: July 2012

Friday, July 27, 2012

Some vbscript utilities

Here are some vbscript code examples.

--Table of contents--
1. Send email
2. Get computer name
3. Connect to database and execute query
4. Restart a windows service
5. Use of a log file
--

1. Send email

Sub sendEmail(ByRef text)
  Dim objMessage
  Set objMessage = CreateObject("CDO.Message")
  objMessage.Subject = "..."
  objMessage.From = "..."
  objMessage.To = "...@..."
  objMessage.TextBody = "...text..."

  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2 
  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "..." 
  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =25 
  objMessage.Configuration.Fields.Update

  objMessage.Send
End Sub

2. Get computer name

Dim objShell, strComputer
Set objShell = CreateObject("WScript.Shell")
strComputer = objShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
Set objShell = nothing

3. Connect to database and execute query

Option Explicit

on error resume next
Set objShell = CreateObject("WScript.Shell")

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=<db>","<username>","<password>"

if err.number <> 0 then
    WScript.Echo "error " & hex(err.number) & ": " & err.description 
else
    WScript.Echo "value = " & getVal()
    objConnection.close
end if
on error goto 0

Set objConnection = Nothing

Set objShell = nothing
WScript.Quit(0)

Function getVal()
    Dim sql, strWatchFile
    sql = "select val from table"
    set strWatchFile = objConnection.Execute( sql )
    if err.number <> 0 then
        WScript.Echo hex(err.number) & vbcrlf & err.description 
        getVal = -1
        Exit Function
    else
        getVal = strWatchFile(0).value
        strWatchFile.close
    end if
    set strWatchFile=nothing
End Function

4. Restart a windows service (by calling the batch file below)

objShell.Run "restart.bat ""<service name>""", 0, true

The three parameters are:
  1) string: shell command, a batch file in this case (plus its parameter).
  2) int: 1 - show window, 0 - hide window.
  3) boolean: true - wait until the shell command ends, false - do not wait.

5. Use of a log file

Dim objFileSystem, objLogFile, logFileName, useLog

useLog = True
logFileName = "C:\mylog.log"
Set objShell = CreateObject("WScript.Shell")

openLog()
writeLog("hello world")
closeLog()

Set objShell = nothing
WScript.Quit(0)

Sub openLog()
  If NOT useLog Then Exit Sub
  Const OPEN_FILE_FOR_APPENDING = 8
  Set objFileSystem = CreateObject("Scripting.fileSystemObject")
  If NOT objFileSystem.FileExists(logFileName) Then
    Set objLogFile = objFileSystem.CreateTextFile(logFileName, TRUE)
  Else
    Set objLogFile = objFileSystem.OpenTextFile(logFileName, OPEN_FILE_FOR_APPENDING)
  End If
End Sub

Sub writeLog(txt)
  WScript.Echo txt
  If useLog Then objLogFile.WriteLine(date & " " & time & ": " & txt)
End Sub

Sub closeLog()
  If NOT useLog Then Exit Sub
  objLogFile.Close
  Set objLogFile = Nothing
  Set objFileSystem = Nothing
End Sub

Restart windows service in a batch file

::
:: usage: restart.bat <service name>
:: Note quotation marks can be used if the service name contains space.
::
:: This batch script restarts a windows service (given as a parameter of the batch file).
:: It does this by:
:: 1) issue a stop command;
:: 2) check the status of the service, go back to 1) if it is not stopped yet;
:: 3) when the service has been stopped, restart it.
::
:: Note: the provided service MUST exist, otherwise it will get into an infinite loop.
::
:: References:
:: [1] http://serverfault.com/questions/25081/how-do-i-restart-a-windows-service-from-a-script
:: [2] http://boards.straightdope.com/sdmb/showthread.php?t=458812
:: [3] http://www.robvanderwoude.com/errorlevel.php
::
::

@ECHO OFF

if [%1]==[] goto end

:stop
sc stop %1

rem cause a ~2 seconds sleep before checking the service state
ping 127.0.0.1 -n 2 -w 1000 > nul

sc query %1 | find /I "STATE" | find "STOPPED"

if errorlevel 1 goto :stop
goto :start

:start
sc start %1

:end

Disable Visual Studio JIT debugger

Sometimes when a program gets into an exception or runs into any error, a dialog box will pop up saying "An unhandled exception ('...') occurred in ...exe." This is annoying at run time, say, when this is a windows service and you want it to run, and handle any error by program without human intervention. To disable it, there are two ways:

1) disable in VS.NET: Tools -> Options -> Debugging -> JIT, deselect native/managed/script
2) disable in registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\DbgManagedDebugger
   For 64-bit operating system, delete the following registry keys also:
     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\DbgManagedDebugger
   I did 1). Wait to see if it still happens. If it still happens do 2). This should fix it!
       
See:
[1] http://weblogs.asp.net/fmarguerie/archive/2004/08/27/how-to-turn-off-disable-the-net-jit-debugging-dialog.aspx
[2] http://msdn.microsoft.com/en-us/library/k8kf6y2a%28v=vs.80%29.aspx
[3] http://msdn.microsoft.com/en-us/library/5hs4b7a6%28v=vs.90%29.aspx