SQL Server Reference Guide

The military, like all government agencies, loves paperwork. When I was in the Air Force, we used to say that perfect government job equilibrium is achieved when you spend 100% of your time documenting the job you aren't doing.

While too much government paperwork isn't a good thing, documentation is good for maintenance programs. In the last article on SQL DMO Coding, I showed you a maintenance program that backs up a database, updates statistics, and checks the tables and indexes. What it lacks is a method to provide adequate feedback and a historical record of the maintenance. I'll add that code today to make a complete maintenance program.

The method for creating a log file is to open a FileSystem object, tie it to a file, write to the file, and then close the file. While that works, it still isn't complete enough for our needs. I'll show you my preferred method for the logging process, although there are others.

First, let's take care of the basics. I need to set aside four new variables to start. The first two are objects that represent the file system and the file. You can use the FileSystem object as the parent (just like the SQL DMO parent is for our server) and the File object as the child. I'll move to the "Variables" section of the code from the last article and type those in:

Dim oFileSystem   ' File System Object for logs
Dim oLogFile    ' File object

The next variable I set holds the logging file's name. You might be tempted to hard-code the name when you create the object, but making a variable allows you to ensure unique log files by prepending the system name or date and time. It also gives you the freedom to let the user set the location, if that's more in keeping with your code distribution. I'll add that variable now:

  Dim FileName ' File name variable

Finally, I create a variable for each line in the log file. I'll explain that in a moment:

  Dim strMessage ' Logging Message

Now that the variables are set aside, I use the FileSystem object mentioned earlier to create the file:

' Set up Logging File
  FileName = "c:\temp\Maintenance.log"
  Set oFileSystem = CreateObject("Scripting.FileSystemObject")
  Set oLogFile = oFileSystem.OpenTextFile(FileName, 8, True) 

In this example, I set the FileName variable to a constant location. You might want to allow the user to pick a location, or perhaps use the Date() function (with a little formatting to remove the illegal / characters) to create multiple logs, in case they run the code multiple times.

The next line sets the file system object. There's nothing to modify here; it's just the code that creates the FileSystem object.

The next line needs a bit of explaining. The first part, oFileSystem.OpenTextFile, calls a method on the oFileSystem object to open a text file. The parts we're more interested in are the three variables that follow.

The first is the filename, and for this I use the FileName variable I created earlier. The number that follows is the way the function should open the file. Here are the codes you can use and what they mean:

• 1=ForReading: Open a file for reading. You cannot write to this file.
• 2=ForWriting: Open a file for writing.
• 8=ForAppending: Open a file and write to the end of the file.

The True that follows the number tells the ForAppending mode that the file should be created if it doesn't exist.

I place this code just before I notify the user about the maintenance. There's a reason for that. I want to know if the user started the program and then just bailed out. You might not care about that distinction, but the point is to make sure that you think about the placement of each section of your code.

So now we have a file object that we can write to. Here's the code to write a line containing "Text to Write" in the log file:

oLogFile.WriteLine("Text to Write")

I also want to add the current time to each line. By placing an entry before and after each operation I can get a historical timeline for each section of the maintenance. I can also use these as entries for a log-parsing program later.

The modified line looks like this:

oLogFile.WriteLine(Time() & "-" & "Text to Write")

I used the Time() function along with some formatting to add the system time in the log, plus a dash to enable parsing later. You can modify this to any format you want.

I could repeat that line for each entry in the log file, but that means I'd have to type that long string each time I update the log file. Plus, if I ever want to change the format of the time or change the dash to a colon, I'll have to make sure I locate each one of these lines and edit them. This leaves me open to errors.

A better method is to create a reusable piece of code called a subroutine, or a function. We won't go into the differences here, but the upshot is that a subroutine or function is like a small program within a program. You can "call" these routines to do repetitive work within your code. Otherwise, they sit idle. They are also very easy to create and use.

Down at the bottom of my code, just before the end of the program, I create a subroutine:

Sub LogProcess()
End Sub

That's the format of a subroutine. Enter the name you want prefaced by Sub and then close it out with open and closed parenthesis marks – like this: (). On the last line, place the words End Sub.

That's all you need to make a subroutine (albeit one that doesn't do anything). To make it more useful, I place some code between the two lines:

Sub LogProcess(strMessage)
 oLogFile.WriteLine("Text to Write")
End Sub

This subroutine will now write the line "Text to Write" each time it is called. To call it, just type the line:

LogProcess()

Anywhere in the code. Of course, I don't really want the words "Text to Write" recorded over and over in our log file, so let's dress this subroutine up a bit.

What I want is to pass some text to the subroutine, and have it write that to the file. To do that, I need to tell the subroutine to expect some text:

Sub LogProcess(strMessage)

All this requires is a variable name – here it's strMessage.

Now I can use the variable within the subroutine:

Sub LogProcess(strMessage)
 oLogFile.WriteLine(Time() & "-" & strMessage)
End Sub

And when I call the function, I use this format:

LogProcess("Checking the tables..") 

You can see that the function name, with the parenthesis, activates the subroutine. The string "Checking the Tables.." is the text I want to have entered into the log file. The subroutine takes it from there.

This really all comes together when you see the complete code:

' DMOMaintenance.vbs
' Created:     1/10/05 - Buck Woody
' Change Log:    1/17/05 - Added logging capability

' Variables
  Dim oServer    ' SQL Server object
  Dim oDatabase  ' Database object
  Dim oTable    ' Table object
  Dim oIndex    ' Index Object
  Dim oFileSystem  ' File System Object for logs
  Dim oLogFile  ' File object
  Dim ReturnVal  ' Object to hold the user's choice
  Dim FileName ' File name variable
  Dim strMessage  ' Logging Message
  
' Set up Logging File
  FileName = "c:\temp\Maintenance.log"
  Set oFileSystem = CreateObject("Scripting.FileSystemObject")
  Set oLogFile = oFileSystem.OpenTextFile(FileName, 8, True) 
    
'Provide feedback to the user, allow them to bail
  ReturnVal = MsgBox("Checking Databases, Tables and Indexes. " _
  & "Click Yes to continue, No to Cancel.", _
  VBYesNo, "DMO Maintenance")

If ReturnVal = VBYes then

  MsgBox "Maintenance has started." _
  & " You'll receive a confirmation when" _
  & " the maintenance is complete. Click" _
  & " OK to begin.", VBOK

  ' Create and connect to a server
  Set oServer = CreateObject("SQLDMO.SQLServer")
  oServer.LoginSecure = True
  oServer.Connect "(local)"
  LogProcess("Local Server connected.")

  ' Perform Server maintenance
  ' Place any server maintenance you want here.

  ' Perform database maintenance
  ' Get a database. Could prompt here or use a 
  ' collection to do them all.
  Set oDatabase = oServer.Databases("pubs") 
  LogProcess("Pubs Database connected.")

  ' Backup the database. Use an in-line
  ' Backup object method.
  LogProcess("Database Backup...")
  Dim oBackup
  Set oBackup = CreateObject("SQLDMO.Backup")
  oBackup.Action = SQLDMOBackup_Database
  oBackup.Database = "pubs"
  oBackup.Files="c:\temp\pubs.bak"
  oBackup.SQLBackup oServer
  LogProcess("Database Backup Complete.")

  ' Perform Table maintenance
  LogProcess("Checking Tables...")
  oDatabase.CheckTables()
  LogProcess("Tables Checked.")

  LogProcess("Updating Statistics...")
  oDatabase.UpdateIndexStatistics( )
  LogProcess("Statistics Updated.")

  For each oTable in oDatabase.Tables 
    ' Don't really need this,
    ' since we did it three lines above.
    ' Just another method if you want
    ' to do it table by table.
    LogProcess("Updating Table " & oTable.Name)
    oTable.UpdateStatistics()

    ' Perform Index maintenance
    For each oIndex  in oTable.Indexes
      IndexList = IndexList & "Index Checked: " & oTable.Name & " - " & oIndex.Name
      LogProcess(IndexList)
      oIndex.CheckIndex()
    Next
  Next

  ' Notify the user you're done.
  LogProcess("Maintenance Complete.")
  MsgBox "Maintenance complete. See log file " & FileName

  ' Clean up.
  Set oTable = Nothing
  Set oDatabase = Nothing
  Set oServer = Nothing
End If

oLogFile.Close

Sub LogProcess(strMessage)
 oLogFile.WriteLine(Time() & "-" & strMessage)
End Sub

If you run this code, make sure you have a C:\TEMP directory on your system. Once you run the code, you'll get a log file stamped onto that directory. Note that the log file is created where the code runs, not the server it runs against. That's an important distinction.

In the next set of articles, I'll use these logging techniques again. In future articles, I'll explain how you can go the opposite way with the file and read it with your programs.

Online Resources – Data Management Objects

The full documentation for the SQL Server Object Library can be found here.

I've been reading a lot of Perl programming lately, and thought I'd share this tidbit on creating log files.

InformIT Tutorials and Sample Chapters

If you're using a 3-GL language (such as one of the .NET flavors) you can also write significant entries to one of the event logs that Windows maintains. This article by Jim Mischel shows you how.