SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using VBScript to Automate Tasks

By Haidong Ji, (first published: 2003/11/04)

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
		End If

'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")
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


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.


Total article views: 100397 | Views in the last 30 days: 23
Related Articles

Scripting SQL Cluster Shutdown



vbscript for dtspackage



What is the BEST way to automate tasks on your server?

Batch files/Command line? VBScript? PowerShell? Winautomation?


VBScript Classes to Query SQL Server for Backup Information

A VBscript class is created that can be used to query the maintenance plans on an SQL server to dete...