During the course of your career, you will be asked to do small miracles for the company that you work for. The worst part of this, is that you may be out with your family at Disney, Chuck-e-Cheese, or the local pizza place when you get the page, or phone call. Regardless of how you get notified, it becomes extremely difficult to suddenly leave your family when an emergency arises. Some emergencies can't be helped, others are likely nuisances that have developed through no fault of your own, but still need to be addressed.

What to do?

Like most folks, if you value your career, there is no question on what needs to be done. You have to leave and fix the issue. That is simply a fact of life. Many developers and DBA's simply go home to VPN into work, or go to work directly to resolve the problem. I have done both and continue to do so. With a few tools that I now employ, however, I can do this less often than I used to for specific small problems, and many times I can continue to be with my family.

Easy Scripting Sample

Most of us by now know that scripting using VBScript can automate some of our day to day tasks. I use it for repetitious tasks that need to be performed. Let's start with email. Sometimes I need to send out a simple email, and I am not around my computer to do so. Maybe I am at a server that doesn't have an email client. I log in to a shared drive and double click my email script file, and poof. Email is sent.

So what does the script do? How does it work? Does it use exchange? The email script simply executes a stored procedure in my personal SQL Server that does the heavy lifting, provides the security I need since I don't want anyone except for myself sending emails, etc.

What needs to be running on my computer for this to work? IIS, SMTP, and SQL Server.

My domain name also needs to have admin privileges on my personal SQL Server.

Email.vbs code:
''''''''''''''''
'Open connection
Dim DBConn
Set DBConn = OpenConn()
'Create a new DBF file named Persons.DBF
DBConn.Execute "EXEC sp_SMTPemail 'MyEmail@Mycompany.com'
     , 'SomeBody@Mycompany.com', 'Subject message here','Body of message goes here'"
set DBConn =nothing
Function OpenConn()
  Dim Conn: Set Conn = CreateObject("ADODB.Connection")
  Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDATABASE;Data Source=MYCOMPUTER" 
  Set OpenConn = Conn
End Function
''''''''''''''''
Stored Procedure code to create in MYDATABASE used by script:
-----------------------------
CREATE PROCEDURE sp_SMTPemail
(
@From as nvarchar(50),
@To as nvarchar(50),
@Subject as nvarchar(255),
@Body as text 
) 
AS
-- Declare 
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object
-- Configuration Object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'
-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', @From
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'
-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT 
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
select @hr
----------------------------------------

The code above will have you sending emails from almost anywhere within your network without a problem. Because of windows security, you don't have to worry about others trying to run it. This is an example of a scripting tool. As you can see the stored procedure uses sp_OACreate to call objects outside of SQL Server to email your message, although the sp_OACreate is outside the scope of this article, I have to say that it is one of the most powerful tools available, and if you can copy-paste, you can easily modify the code within this article for your own use.

WMI Scripting Sample

So what is WMI? The Definition from Microsoft says "Windows Management Instrumentation or WMI is a component of the Windows operating system that provides management information and control in an enterprise environment. By using industry standards, managers can use WMI to query and set information on desktop systems, applications, networks, and other enterprise components. Developers can use WMI to create event monitoring applications that alert users when important incidents occur."

It's a mouthful, but the gist of it is that it can help you monitor, stop, start, and edit files and services across the network. Sounds nice!

So how can we use it? Below is a sample WMI Script that looks at a computer across the network and stops a process (executable or service).

KillProcess.VBS Script
''''''''''''''''''
Const PROCESS_NAME = "MYPROCESS"
Dim colResults, objProcess, objWMI, strWQL

'Set objWMI = GetObject("WinMGMTS:Root/CIMv2") 'Used only for local process
set lctr = CreateObject("WbemScripting.SWBemLocator")  
set objWMI = lctr.connectServer("10.20.10.100", "root\cimv2",".\UserName", "Password")  
strWQL = "SELECT * FROM Win32_Process WHERE Name = '" & PROCESS_NAME & "'"
Set colResults = objWMI.ExecQuery(strWQL)
For Each objProcess In colResults
   objProcess.Terminate
Next
'''''''''''''''''''''

First, WMI scripts are powerful, but the drawback is that if you create a script to use across the network, you may need to show a domain username and password which is unacceptable. Especially if the script is put into a shared drive that you would like to access across the network, and want to give someone access to if you are out sick, or for whatever other reason. To secure the script(s) that you would like to use it is suggested that you first create a local folder on your machine that only you (your domain account) has access to. We will use "C:\LocalSecureDirectory" as our directory. Then as you create all your WMI scripts, simply place them into this one secure directory.

So how do I run them across the network, and maybe allow my fellow coworker access to run one or two of them when he/she needs to? The solution again, is to use SQL Server like we did above to send an email message. Below is a SQL Server stored procedure that allows you to run a VBS script in the secure directory "C:\LocalSecureDirectory":

sp_RunVBS Stored Procedure
--------------------------
CREATE PROCEDURE sp_RunVBS(@cmd VARCHAR(255), @Wait INT = 0) AS
--Create WScript.Shell object
DECLARE @result INT, @OLEResult INT, @RunResult INT
DECLARE @ShellID INT
Declare @Folder varchar(255)
Select @Folder = '"C:\LocalSecureDirectory\'
Select @cmd = @Folder + @cmd + '"'
--select @cmd --test
EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult 

EXECUTE @OLEResult = sp_OADestroy @ShellID
RETURN @result
------------------------------------------

What does this procedure give you? It allows you to run VBScript files using a stored procedure within SQL Server 2000. This actually is pretty powerful in itself, as you can simplify coding and calling external objects using VBScripts rather than the sp_OACreate stored procedure which is often used.

Once this stored procedure is compiled, you can go to your shared drive, which is accessible across the network, and create a VBScript file that runs this procedure using NT security. You can give this script out to your buddies, add them to your personal SQL Server as users, and control their access simply by adding or taking away execute privileges to the procedure under SQL Server.

The script below shows how to execute the WMI script by simply passing the script name to the procedure we just compiled.

RunKillProcess.vbs
'------------------
Dim sScriptName, Log
Log = "C:\LocalShare\RunKillProcess.txt"
sScriptName = "KillProcess.vbs"

'Open connection
Dim DBConn, RsError, x, Error
Set DBConn = OpenConn()
'Executes VBScript in secure folder using windows security
set RsError = CreateObject("ADODB.recordset")
RsError.Open "EXEC sp_RunVBS '" & sScriptName & "',0", DBConn
'All items below this are used for logging errors to my normal share folder "C:\LocalShare"
'Error Stuff, All procs need at least a return value of 0
for each x in RsError.fields
Error= x.value 
next
'Set File Logging
dim oFS1 
'Create FileSystem Object
Set oFS1 = CreateObject("Scripting.FileSystemObject")
'Set Logging
bLogging = true
if bLogging Then
Set oLog = oFS1.CreateTextFile(Log, true) 
End if
If Error = "0" Then
    'No error, do nothing
    LogMessage "No Errors."
Else
    'Wscript.Echo "Error " & Error & "."
    'msgbox "Error " & Error & "."
    LogMessage "Error " & Error & "."
End If
'Destroy components
set oFS1 = nothing
set DBConn =nothing
set RsError =nothing
Function OpenConn()
  Dim Conn: Set Conn = CreateObject("ADODB.Connection")
  Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Magic;Data Source=ALOERA1" 
  Set OpenConn = Conn
  set Conn = nothing
End Function
Sub LogMessage(sMessage)
'WScript.Echo sMessage
'oLog.writeline "(" & Now() & ") " & sMessage
oLog.writeline sMessage
End Sub
Sub ShowMessage(sMessage)
WScript.Echo sMessage
'oLog.writeline Now() & " - " & sMessage
End Sub
'---------------------------------------------------------------

As you can see we have a powerful tool that we can make use for administrative tasks and small emergencies. We can run the script across the network, give it out without giving up security, and add a large piece of functionality to SQL Server. I use this method to provide myself tools that I can access anywhere. For more on WMI Scripting, go to Microsoft: http://msdn.microsoft.com/library/en-us/wmisdk/wmi/wmi_tasks_for_scripts_and_applications.asp

I believe we have barely touched the tip of what can be accomplished here. To show what I mean, I will give you one last sample that you may be interested in. Remember that toward the beginning of this article, we imagined getting a call, or a page from work while we were out with our family...

I personally have a sidekick phone from t-mobile that I use for email, but I am sure if you have a pager with email functionality or some such device, the following can work for you, too. In Outlook, you can setup a rule that allows you to run a VBA function when a message arrives with "@SomeThing" in the subject of the message. You would first add a piece of code to outlook by going to Tools->Macro->Visual Basic Editor

After you open the editor, you can cut -paste the following:

'VBA Code
'--------------------------------------------------
Sub CustomMailMessageRule(Item As Outlook.MailItem)
   Select Case Item.Subject
        Case "@Something"
            RunScript "Something.vbs"
        Case "@otherthing"
            RunScript "otherthing.vbs"
   End Select
End Sub
Private Function RunScript(MYScript As String)
'Open connection
Dim DBConn, RsError, x, Error
Set DBConn = OpenConn()
'Executes VBScript in secure folder using windows security
Set RsError = CreateObject("ADODB.recordset")
RsError.Open "EXEC sp_RunVBS '" & sScriptName & "',0", DBConn
Set RsError = Nothing
Set DBConn = Nothing
End Function
Function OpenConn()
  Dim Conn: Set Conn = CreateObject("ADODB.Connection")
  Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDATABASE;Data Source=MYCOMPUTER"
  Set OpenConn = Conn
End Function
'----------------------------------------------------------

At this time we have not put in any code for errors or logging in this example, but they should also be added. We save this and close the VB Editor.

Next we go back to Outlook and select Tools-> Rules and Alerts. Here we create a new rule stating that any message with a subject of "@" should run the new script "CustomMailMessageRule". As you can see, this opens up lots of great possibilities for automation, as we can specify in our CustomMailMessageRule, specific scripts we can run when an email comes in. Of course this information cannot be shared, as it would allow people to simply send you an email to activate a script.

Lets get back to where I got paged over a minor item while I was at Chuck-e-Cheese. In this situation, I would simply send an email to myself to run a script, the script would run a process and email me back the results of the process after it has finished. The nice part is that I can do this all while I am looking at a big dancing mouse with my family.

About the Author

Anthony Loera has been developing for more than 10 years. He has developed database applications for Verizon, Merck pharmaceuticals, Americatel Telecom, Microsoft, IDS Telecom, and Vitas Hospice Healthcare. He can be reached at his email: Brainclone @ Gmail.com