SQL Server Reference Guide

Data Management Objects: Multiple Row Returns

Last updated Feb 18, 2005.

In the Data Management Objects coding that I've shown you in the last few articles, I originally focused on running commands that didn't need permanent output. These commands include maintenance and information regarding the server, in which showing the information on-screen was the only requirement.

Along the way, I showed you how to output that information to a text file, which allows a historical reference. In the last article, I also showed you how to retrieve a single row and column of data from a T-SQL query.

In this article, I'll put it all together and show you how to make a truly extendable piece of code. When complete, this code allows you to have a "driver" file with any T-SQL statements you wish, which outputs the results of that T-SQL both to the screen and to a results file.

As always, the place to begin coding is with the requirements. I'll then create my comments and then code the comments.

The requirements for this code in bullet form are:

• The code should accept input from a text file.
• It should allow any valid T-SQL statements.
• It should connect to a particular server and database.
• It should return the results of the query to the screen.
• It should return the results to a file.

These are simple requirements, and as such, you might be tempted to leave out this step. Don't do it! It's always best practice to do a requirements layout, even one as simple as this.

Next, I create my comments. I do this by laying the process out in blocks, such as "create variables" and "Input and output files." Once I begin coding, I might rename these sections or move them around to fit the code.

With that process in mind, I'll show you the complete code here, and then describe the newer sections below:

' dmoQuery.vbs
' Created:    02/18/05 - Buck Woody
' Change Log:   

' Variables
   Dim strMsgBoxText    ' Text Holder
   Dim objServer       ' SQL Server name
   Dim objDatabase   ' Database name
   Dim strSQLStatement   ' SQL query to run
   Dim strQueryResults   ' Results of the query
   Dim objFileSystem   ' File System object 
   Dim objInputFile   ' Input File
   Dim objOutputFile   ' OutputFile
   Dim strMessage      ' Logging Message
   Dim intRowNumber   ' Current row number placeholder
   Dim intColumnNumber   ' Current column number placeholder
   
' Input File
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 Set objInputFile = objFileSystem.OpenTextFile("c:\temp\SQLInput.txt", 1) 
' Fill the SQL Statement to run
 strSQLStatement = objInputFile.ReadAll 

' Output File
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 Set objOutputFile = objFileSystem.OpenTextFile("c:\temp\SQLOutput.txt", 8, True) 

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

' Connect to the database
 Set objDatabase = objServer.Databases("pubs")
 Set strQueryResults = objDatabase.ExecuteWithResults(strSQLStatement)

' Do the work
 For intRowNumber = 1 to strQueryResults.Rows
    For intColumnNumber = 1 to strQueryResults.Columns
      strMsgBoxText = strMsgBoxText & VBTAB & strQueryResults.GetColumnString(intRowNumber, intColumnNumber)
    Next    
   strMsgBoxText = strMsgBoxText & VBCRLF 
 Next 

' Show the message box
 MsgBox strMsgBoxText

' Output to Results file
 objOutputFile.WriteLine(strMsgBoxText)

' Clean up
 Set objDatabase = Nothing
 Set objServer = Nothing

For the most part, the code follows along with what I've shown you before. Looking at the outer "skeleton," I set up the variables, connected to a server and database, left some space in the middle to work, and then closed the database and server objects.

The "work" section contains the new parts. Let's begin with the Input File part:

' Input File
 Set objFileSystem = CreateObject("Scripting.FileSystemObject")
 Set objInputFile = objFileSystem.OpenTextFile("c:\temp\SQLInput.txt", 1) 
' Fill the SQL Statement to run
 strSQLStatement = objInputFile.ReadAll 

A few articles back, I showed you how to work with files. At that time, we were mostly interested in writing files, and we've duplicated that same kind of code in the Output File section which follows. In this section, however, we're more interested in reading a file. As with the output file, I create a FileSystem object and open a text file. Notice that the mode option changes to 1 – which is all that's needed to read a file.

The other difference is that, instead of writing to the file with the WriteLine method, I use the ReadAll method on the file object there in the 5^th line of the code snippet shown above. I assigned the output of that method to a string of text, called strSQLStatement, so I can use it later. This satisfies the first requirement. Whatever is in the file c:\temp\SQLInput.txt will load into this string.

The work section has the engine of the program. Let's take a look:

' Do the work
 For intRowNumber = 1 to strQueryResults.Rows
    For intColumnNumber = 1 to strQueryResults.Columns
      strMsgBoxText = strMsgBoxText & VBTAB & strQueryResults.GetColumnString(intRowNumber, intColumnNumber)
    Next    
   strMsgBoxText = strMsgBoxText & VBCRLF 
 Next 

The first thing I do is set up a loop – that's the outer For..Next part – that contains the rows I will work with. This causes the program to iterate through all of the rows of the query. It does that by using a property of the strQueryResults object called Rows, which contains the total number of rows in the query.

Right away I set up another loop. This is because within each row of data, there are multiple (as yet unknown) columns of data. I use the same process to iterate through all the columns as I did for the rows. Pretty neat!

The point of all this iteration is to stuff the results into a string, which is what the 4^th line in the code snippet does. The 6^th line adds a final carriage-return to the row of data, so it is placed before the Next that handles the rows.

The rest you've seen before, such as the message box output to the screen and the file write.

You can use this same process to iterate through any row and column based set, and to create a file based on the results.

The input file I used looks like this:

SELECT * 
FROM authors
GO

You can use multi-line inputs if you wish, but include a GO between block statements.

One final word about this code: if you're returning a lot of data, remove the lines that read:

 ' Show the message box
 MsgBox strMsgBoxText

Message boxes don't have scrollbars, and just aren't suited for large sets of data. Instead, send the output to a file or use another text object to handle the results.

Online Resources

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