SQL Server Reference Guide

Data Management Objects: Running SQL Statements

Last updated Feb 11, 2005.

It might seem strange that in the previous articles on DMO programming, I've shown you how to get a great deal of information about SQL Server databases and their objects, how to perform maintenance on a system, and even how to use the Filesystem object to create a log file for the process. However, we haven't seen a single T-SQL query yet. It's great to be able to manipulate various DMO objects, but you just can't beat T-SQL for power and flexibility.

In this article, I show you one way you can run a T-SQL statement against SQL Server. I say one way since there are a few methods you can use to run a query, each with its own advantages and disadvantages.

I'll begin this exercise the same way I begin any programming: with a clear definition of the goal, or the requirements.

The requirement for this code is pretty simple. I want to run a T-SQL query and return one result. This type of return is called scalar. (Later, we'll learn ways to get lots of data from the server.)

The method I use today uses the Databases object. In DMO programming, you work with objects and collections of objects. Most of the time, those things are separate, but in some cases the distinction isn't so clear. Using a particular database is one of those times.

To work with a database, you create a SQL Server object, and then access the Databases collection it holds. From there, you create a variable that stores a particular database from that collection. I do that here; once I have a handle to the database I use a method to run a query.

But I get a little bit ahead of myself. The way I code is to detail all the requirements, create comments, and then code the comments. For brevity's sake (and to stay on task) the requirements we concern ourselves with here consist of getting a single result back from the database. That's as much detail as we need for now.

The comments you'll see right in the code:

' dmoQuery.vbs
' Created:    01/27/05 - Buck Woody
' Change Log:   

' Variable to hold Server Name
Dim ServerNameVar

' Variable for the text boxes
Dim MsgTxtVar

' Variables for objects
Dim oServer
Dim oDatabase
Dim oQeryResults

' Create the server object using SQL-DMO
Set oServer = CreateObject("SQLDMO.SQLServer2")
' Login with current Windows account
oServer.LoginSecure = True
oServer.Connect "(local)"

' Connect to the database
Set oDatabase = oServer.Databases("pubs")
Set oQueryResults = oDatabase.ExecuteWithResults("SELECT au_fname FROM authors ORDER BY au_fname")
MsgTxtVar = oQueryResults.GetColumnString(1, 1)

' Get the message box
MsgBox MsgTxtVar


' Clean up
set oDatabase = Nothing
Set oServer = Nothing

Most of this code you've seen before, but with slight differences. I left the variables in place, even though I left out the other sections I mentioned. That makes it easier to correct mistakes, even in this small example.

Connecting to the server object is just as we've seen before. The part that begins to get interesting is when I set up the database object:

' Connect to the database
Set oDatabase = oServer.Databases("pubs")
Set oQueryResults = oDatabase.ExecuteWithResults("SELECT au_fname FROM authors ORDER BY au_fname")

I set a static reference to a database (pubs) and then used a new object variable, called oQueryResutls, to store the query. This creates a single object that represents the results of a query, called by a method on the oDatabase object.

There are a couple of methods you can use to call a query, and here I use one called ExecuteWithResults. This method does just what it sounds like: it executes a query, and then returns a result set.

Notice that the parameter it takes is a query (in quotes). In this case, I asked for the first name of all the authors, ordered by the first name, from the authors table. This example is simple, but just about any valid SQL Server query can be used here.

I've run a query and stored the result in an object, and now I want to display that result in a message box. The result, however, isn't simply a string of text. It's actually still stored as a dataset, consisting of rows and columns, like a spreadsheet. I can't display all of the text by simply referring to the single variable where I stored the data, since the system isn't sure which specific row or column I'm looking for.

The code that takes care of that specification is here:

MsgTxtVar = oQueryResults.GetColumnString(1, 1)

What I've done in this part of the code is to set a text variable to only a part of the oQueryResults object, using yet another method. This seems strange at first, since the oQueryResults object was itself filled with a method! As Alice in Wonderland observed, things sometimes refer back to themselves in the strangest ways. Programming is like that; you sometimes have to work back through the code to understand how some variable suddenly has methods and properties.

It is this ability of the oQueryResults object to inherit all the properties and methods of a QueryReults object that allows us to get at a particular column and row – which is precisely what the (1,1) part does. Since I asked for column 1, row 1, and the query got the au_fname column sorted by au_fname, I get the first alphabetical, single item from the table.

Now that I have that information stored in a string, I can use it for whatever I want. In this example, I display the data in a message box, but I could also use it in a comparison, to trigger another method, and more.

In this article, I've shown a scalar (single value, in the physics sense) result. In the next article in this series, I'll show you how to deal with more data, iterating through the entire dataset, regardless of length or width.

Online Resources

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

InformIT Tutorials and Sample Chapters

Safari has literally hundreds of books that explain object oriented programming. Make sure you check out this incredible resource.