SQLServerCentral Article

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.

Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Customize values here to fit your needs
iDaysOld = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files
'Walk through each file in this folder collection. 
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next
'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing

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.

Option Explicit
Dim objFSO, objMyFile, objShell, strFTPScriptFileName, strFile2Get
Dim strLocalFolderName, strFTPServerName, strLoginID
Dim strPassword, strFTPServerFolder
'Customize code here to fit your needs
strLocalFolderName = "My Folder Name where we put the file to be FTPed"
strFTPServerName = "FTP Server Name"
strLoginID = "FTP Server Login ID"
strPassword = "FTP Login ID Password"
strFTPServerFolder = "Folder Name on FTP server where the file resides"
'The following code converts date to the right format, YYYYMMDD
strFile2Get = DatePart("yyyy",Date)
If DatePart("m",Date) < 10 Then
strFile2Get = strFile2Get & "0"
End If
strFile2Get = strFile2Get & DatePart("m",Date)
If DatePart("d",Date) < 10 Then
strFile2Get = strFile2Get & "0"
End If
strFile2Get = strFile2Get & DatePart("d",Date)
'The following code generates the file name on the FTP server you want to get
strFile2Get = "Data" & strFile2Get & ".csv"
'The follow lines of code generate the FTP script file on the fly,
'because the get file name changes every day
strFTPScriptFileName = strLocalFolderName & "\FTPScript.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (objFSO.FileExists(strFTPScriptFileName)) Then
    objFSO.DeleteFile (strFTPScriptFileName)
End If
Set objMyFile = objFSO.CreateTextFile(strFTPScriptFileName, True)
objMyFile.WriteLine ("open " & strFTPServerName)
objMyFile.WriteLine (strLoginID)
objMyFile.WriteLine (strPassword)
objMyFile.WriteLine ("cd " & strFTPServerFolder)
objMyFile.WriteLine ("ascii")
objMyFile.WriteLine ("lcd " & strLocalFolderName)
objMyFile.WriteLine ("get " & strFile2Get)
objMyFile.WriteLine ("bye")
objMyFile.Close
Set objFSO = Nothing
Set objMyFile = Nothing
'The following code executes the FTP script. It creates a Shell
'object and run FTP program on top of it.
Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run ("ftp -s:" & chr(34) & strFTPScriptFileName & chr(34))
Set objShell = Nothing

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.

Resources

Rate

3.8 (10)

You rated this post out of 5. Change rating

Share

Share

Rate

3.8 (10)

You rated this post out of 5. Change rating