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

Overtime work not cost-effective

Why Crunch Modes Doesn't Work: Six Lessons. By Evan Robinson.

Chinese version: 加班赶工，得不偿失

Octave - open source version of Matlab

Octave notes

Raid 0/1/3/5/10

Standard RAID levels
Raid
What is "RAID" and why is "RAID 0" such a bad, bad idea?

More T-SQL

- In T-SQL, string comparison is NOT case-sensitive.
  IF 'asc' = 'ASC' PRINT 'equal'
  ELSE PRINT 'NOT equal'
  -- This will out put 'equal'.

- Construct dynamic query and run it in stored procedure. Example:
 
  DECLARE @cmd varchar(200)
  DECLARE @cond varchar(100) = ' WHERE name=''Mary'''
  SET @cmd = 'SELECT * from Users ' + @cond 
  EXEC (@cmd)

- Add a row number in the returned data set:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users

  Note that if "ORDER BY" is used at the end of the query, it should match the
  "ORDER BY" in the ROW_NUMBER() to be fast and starts from 1. E.g.:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID DESC

  But the following will run slow, and does not start from 1 (actually list the last 10 rows of the returned list):

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID ASC

- Create a unique value of type uniqueidentifier.
  See: http://msdn.microsoft.com/en-us/library/ms190348.aspx

  -- Creating a local variable with DECLARE/SET syntax.
  DECLARE @myid uniqueidentifier
  SET @myid = NEWID()
  PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

- Split a string by delimiter ','.
  Reference: http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/    

  DECLARE @loadTable varchar(100) = 'a,b,c,d'
  DECLARE @pos int
  DECLARE @tblResult TABLE (v varchar(10))
  DECLARE @piece varchar(10)

  -- Need to tack a delimiter onto the end of the input string if one doesn’t exist
  IF right(rtrim(@loadTable),1) <> ',' SET @loadTable = @loadTable  + ','

  SET @pos =  patindex('%,%' , @loadTable)
  WHILE @pos <> 0
  BEGIN
    SET @piece = left(@loadTable, @pos - 1)
    INSERT INTO @tblResult (v) VALUES ( @piece ) -- Add to the batch list.
    SET @loadTable = stuff(@loadTable, 1, @pos, '')
    SET @pos =  patindex('%,%' , @loadTable)
  END
    
  select * from @tblResult 

- Temporary table and in-memory table
  1) Temporary table:
  A temporary table is stored in "System Databases\tempdb\Temporary Tables\".

  To declare a table as a local temporary table, use "#" in front of it.
  To declare a table as a global temporary table, use "##" in front of it.
  See Quick Overview: Temporary Tables in SQL Server 2005.

  CREATE TABLE #MyTable1 ( ID int NOT NULL, name varchar(100) )
  INSERT INTO #MyTable1 (ID, name) VALUES (1, 'Mike')
  CREATE INDEX ix_MyTable1ID ON #MyTable1 (ID)
  SELECT * FROM #MyTable1
  DROP TABLE #MyTable1

  The table will be stored as something like "System Databases\tempdb\Temporary Tables\dbo.#MyTable1____________________________________00000002FBEE".

  The table #MyTable1 stays there after the query is done, unless you call the DROP statement.

  Note that if you don't use the "#", then MyTable1 will be created as a physical table in current database.

  2) In comparison, an in-memory table (table variable) can be used this way:

  DECLARE @x TABLE (ID int NOT null, name varchar(100))
  INSERT INTO @x (ID, name) VALUES ('1', 'Mike')
  SELECT * FROM @x

  Note for this one, you can't use index or DROP statement. The table x disappears after the query is done.

- Use cursor
  DECLARE @ID int
  DECLARE @ct int
  DECLARE cs CURSOR FOR SELECT ID FROM table
  OPEN cs

  FETCH NEXT FROM cs INTO @ID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    @ct = @ct + 1 -- keep count of cycles
    PRINT 'cycle: ' + CONVERT(varchar(20), @int)

    FETCH NEXT FROM cs INTO @ID
  END

  CLOSE cs
  DEALLOCATE cs

- ERROR_MESSAGE() - returns error message when @@error != 0

- exception handling in SQL. E.g.:
  BEGIN TRY
    ...
  END TRY

  BEGIN CATCH
    if @@ERROR <> 0 print ERROR_MESSAGE()
  END CATCH

- OUTPUT
  This returns output of a query. The grammar is a little convoluted though. E.g.:

  Declare @sql as nvarchar(512) 
  Declare @params nvarchar(512) = N'@outParam int OUTPUT'
  Declare @out int
  set @sql = 'select @outParam = count(*) from users'
  execute sp_executesql @sql, @params, @outParam = @out OUTPUT   
  print 'out = ' + CONVERT(varchar(100), @out) -- result is in @out

- Get count of rows in each table:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
  INSERT INTO @TableRowCounts ([TableName], [RowCount])
  EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
  SELECT [TableName], [RowCount]
  FROM @TableRowCounts
  ORDER BY [TableName]

- Show space usage:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128),
                              [Rows] VARCHAR(128),
                              [Reserved] VARCHAR(128),
                              [Data] VARCHAR(128),
                              [Index] VARCHAR(128),
                              [Unused] VARCHAR(128)) ;
  INSERT INTO @TableRowCounts ([TableName], [Rows], [Reserved], [Data], [Index], [Unused])
  EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
  SELECT [TableName], [Rows], [Reserved], [Data], [Index], [Unused]
  FROM @TableRowCounts
  ORDER BY [TableName]

- A Visual Explanation of SQL Joins

- Run query on remote linked server, e.g., return a list of databases.
  SELECT * FROM openquery([linked server], 'select name from sys.databases');