As a SQL Server developer or DBA, you may often find yourself in situations where you need to address urgent issues or perform repetitive tasks remotely. This can be challenging, especially when you are away from your computer or spending time with your family. However, with the right tools and techniques, you can automate these tasks and continue to be productive without compromising your personal time.

Using VBScript for Email Automation

VBScript is a scripting language that can be used to automate various tasks, including sending emails. By leveraging VBScript and SQL Server, you can create a simple script that executes a stored procedure to send emails. This allows you to send emails from any server that has access to your SQL Server.

Here’s an example of a VBScript code that sends an email using a stored procedure:

'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

In this example, the script connects to the SQL Server using the provided connection string and executes the stored procedure “sp_SMTPemail” to send the email. The stored procedure uses external objects to handle the email functionality.

Using WMI for Network Administration

Windows Management Instrumentation (WMI) is a powerful component of the Windows operating system that allows you to manage and control various aspects of your network environment. With WMI, you can query and set information on desktop systems, applications, networks, and other enterprise components.

One of the useful applications of WMI is remote process management. You can use WMI to stop, start, and edit files and services across the network. This can be particularly helpful when you need to address issues or perform administrative tasks on remote servers.

Here’s an example of a VBScript code that uses WMI to stop a process on a remote computer:

Const PROCESS_NAME = "MYPROCESS"
Dim colResults, objProcess, objWMI, strWQL

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

In this example, the script connects to the remote computer using the provided credentials and uses WMI to query and terminate the specified process.

Integrating VBScript and WMI with SQL Server

To make these automation tasks more accessible and secure, you can integrate VBScript and WMI with SQL Server. By creating stored procedures in SQL Server, you can execute VBScript files stored in a secure directory and control access to these scripts using SQL Server permissions.

Here’s an example of a SQL Server stored procedure that allows you to run a VBScript file:

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
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)
EXEC sp_OAGetErrorInfo @ShellID, @OLEResult 
EXECUTE @OLEResult = sp_OADestroy @ShellID
RETURN @result

In this example, the stored procedure creates a WScript.Shell object and executes the specified VBScript file using the Run method. The script file is located in a secure directory that only the authorized users have access to.

By combining these techniques, you can create a powerful automation system that allows you to perform administrative tasks and address urgent issues remotely. Whether you need to send emails, manage processes, or perform other network administration tasks, VBScript and WMI integration with SQL Server can provide you with the flexibility and control you need.

Remember to always prioritize your personal time and use automation tools to minimize interruptions and maximize productivity.

About the Author:

Anthony Loera has over 10 years of experience in developing database applications for various companies. He has worked with Verizon, Merck Pharmaceuticals, Microsoft, and other organizations. You can reach him at Brainclone@gmail.com.