DBA VB - DMO For Beginners

Introduction

I haven't done much with DMO, but Andy Warren suggested I do so, so I decided to give it a shot.

As a point of reference, this article assumes you've installed the SQL Client tools, which will contain the DMO libraries and the VB6 development environment (SP5 is the most recent). I did this on a Windows XP Professional workstation.

Getting Started

I started with Andy's article(Introduction to SQL-DMO. A quick read have me a starting point. Next, I opened VB and started a new Standard EXE project. It's a good place to start until you are sure what type of project you want to build.

Set a reference to the DMO library, Open "Project | References". Select Microsoft SQLDMO Object Library. Here's a screen shot:

Now I begin doing stuff...

First a simple login scheme.

To implement this, place a text box for the server name, a command button to login and an exit button (don't forget this one) on a form. (Note the form at the side)

I changed the text of my buttons to keep track of them easier. I changed the caption of each one so that I could keep track of them better. Kind of hard to keep track of which button does what when they say Command1 and Command2. I also changed the text to default to my local SQL Server on my workstation. That way I'm not typing it in every time.

Next I double clicked each button and added the following code to each of the _Click events. I've included a screen shot that shows the _Click() event for each button. Most of this will be written for you by the VB IDE, just add the lines that are indented.

Notice that the value of the text box is being passed into the subroutine call. We now need to write the routine to accept this.

Next I added a simple login procedure. I right clicked the project and selected "Add", then "Module". I accepted the default here to create basically a file that can hold my code. Since I don't want this attached to the form, I chose a module.

The code for the module is listed below. It's a simple procedure that makes a connection to the server using a blank sa password. If you've read my stuff, you know I hate blank password, but I chose this for a reason. Read on...

Here's the code for the basic connection subroutine. I'll explain it below.

Sub SQLConnect(ServerName As String)
' If we cannot connect, we want to trap the error, not display it.
On Error Resume Next
'Create a new DMO reference object.
Dim oSQLServer As New SQLDMO.SQLServer2
' Set a timeout
oSQLServer.LoginTimeout = 10
' Try to connect.
oSQLServer.Connect ServerName, "sa", ""
' If we connect, there is no error. If we fail
' the error is -2147221504
' Trap this and show a message box
If Err.Number = 0 Then
    MsgBox ("Connected")
Else
    MsgBox ("Not connected")
End If
' Cleanup.
' ALWAYS DO THIS. It's easy and it prevents memory leaks and other issues
oSQLServer.DisConnect
Set oSQLServer = Nothing
End Sub

My code is commented pretty well. It's not always, but I try. It's amazing that I can come back to something this simple after a couple months and it takes a minute to figure it out. These comments make it incredibly simple and easy to get back into the mind set I had when I wrote this.

In this procedure, we accept the parameter from the form:

I don't have to set it as a string, but i's easy to do so, so why not do it.

The first step inside the routine is to resume on errors:

I do this so I can trap the error and it doesn't appear as some program error. Good idea to trap errors.

The next line creates a DMO object:

If you don't understand objects, this basically creates a mini-program that has the properties and methods (executable calls) of the DMO.Server2 object. Server2 is what Microsoft called it and you can get a list of the things you can do with this object at MSDN.

Next I set a login timeout of ten seconds:

You can ignore this, but I arbitrarily chose this so that I wouldn't sit around waiting for some long period of time. If you don't include this, you might wait longer.

The next stop is where the program connects to the server:

This method takes 3 parameters. The servername, the user name and the password. I've chosen sa and a blank password. Hmmmmm, not a good choice. Why would Steve do this?

Keep reading.....

Time for error trapping:

If the error number is 0, then I pop a box displaying that fact. Otherwise I popup a box with the opposite value. I've included the actual "unable to connect" error number, but I didn't use it because I wanted a yeah or neah.

Run the sucka

If you press F5 or click the little arrow in the VB toolbar, this project will run and you should see the form above. You can type in a SQL Server name or leave the default if you've changed it. I did that and ....

and after 10 seconds I get this:

Which is what EVERY SINGLE ONE OF YOU SHOULD GET!

Conclusions

Just the beginning of a series on VB for the non-programming DBA. I've done a bunch myself, but not much with VB, so as I've used scripting (VBScript) more and more, I decided to bring you all along. Hopefully you've learned a few things and please let me know if I've missed something.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also

rate this article.

Steve Jones

©dkRanch.net August 2002

Return to Steve Jones Home