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.
