Using VBScript to Automate Tasks

The key to maintain a smooth, efficient SQL Server environment is to be
proactive. We need to constantly look for ways to tighten and harden SQL servers
and improve performances. Using proactive monitoring, when we see any signs of
abnormal behavior, we will investigate and see what the real problem is. We will
find a problem and solve it before a problem finds us. We can nip the problem in
the bud, so to speak.

A key component of proactive monitoring is automation. If we can automate a
lot of routine tasks, we will have time to focus on more important issues. In
addition, automation also frees up time for you as a busy DBA, so you can learn
new things and continuously improve yourself.

Automation tools available for SQL Server are SQL Server Agent, SQL (Stored
Procedures), DTS, ActiveX scripts (VBScript), SQL Server command line tools (OSQL/ISQL),
DOS command batch files, WMI, SQL Mail, to name just a few. They all have their
pluses and minuses. Depending on the situation, one method might be better
suited than others.

In this article, I will give you a very brief introduction of VBScript. I
will then provide you with a couple of examples of using VBScript to automate
file deletion and FTPing files. Hopefully they can give you some ideas on where
and how to use VBScript. Both examples can be modified and put into use easily.

In the next few weeks, I will provide some introductions and examples on
other automation tools, such as WMI, shell command and batch files, Stored
Procedures, etc

What is VBScript

Visual Basic Scripting Edition brings active scripting to a wide variety of
environments, like web programming, Windows management, and SQL Server
management. Unlike VB6 or VB7 (Thank God Microsoft will drop the .NET lingo, I
absolutely hate it. It is a mouthful.), it is not a full-blown, feature rich
programming language. VBScript resembles more VB6 than VB7. If you are already
familiar with Visual Basic or VBA (Visual Basic for Applications), you should
have no trouble learning VBScript. Even if you are a beginner, VBScript is not
that hard and there are plenty of resources available on the web to help you
out.
Andy Warren had an article a few months ago on SQL-DMO. Many ideas and
techniques presented in that article can also be applied in general VBScript
programming.

VBScript has only one data type called Variant. A Variant is a special kind
of data type that can contain different kinds of information, depending on how
it is used. One subtype of variant is Object. When assigning or instantiating
an object (FileSystemObject, File object, etc.), remember to use the SET
keyword. This is very important, especially for beginners.

In SQL Server, you can use VBScript to create an ActiveX task in DTS. You can
also add your script as a step to SQL Server Agent job, just remember to select
ActiveX Script as the step type. The following 2 examples can be used in both
ways.

Example 1: VBScript to delete files in a folder that are certain days old

As part of a disaster recovery plan, you may need to transfer backup files from
one server to another. However, you probably don't want to keep accumulating
backup files that you run out of space on your backup server. In this case, you
want to delete files that are certain days (or weeks) old. The following
VBScript can handle this task nicely. You can customize this code, such as
changing the value of iDaysOld, to fit your needs. Most of the code should be
self-explanatory. For more on disk space management,
see one of my articles published a couple of weeks ago.

Example 2: VBScript to automate FTP files

Many of us work in a heterogeneous technical environment. Our environment may
require us to get files from *nix servers or mainframe. The following example
demonstrate a technique to get a file via FTP, assuming the file you want to get
daily is named as YYYYMMDDData.csv. Because the file name changes daily, so we
generate FTP script files on the fly. The rest of the code should be
self-explanatory.

Conclusion

In this article, I gave a very brief introduction to VBScript and provided 2
working examples. Hopefully they demonstrated enough techniques to get you
started. Stay tuned for more articles from me on other ways to automate SQL
Server management.