HOW TO: List the Results of a SELECT Query on a Table by using SQL-DMO (241246)

SUMMARY

To supplement the SQL Server Books Online topic that is about the use of SQL Distributed Management Objects (SQL-DMO) for querying a system table, this article has a Visual Basic example that lists the contents of a system table in a GUI-component. In the example, you populate a list box with the Media Set names found in the backupmediaset table of the msdb database. This technique is useful when you apply it in a SQL-DMO utility for managing Microsoft Data Engine (MSDE), which does not come with a GUI-based management interface.

How to List the Results of a SELECT Query by Using SQL-DMO

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Code Example

The code example that follows assumes that:

There is a command button named Command1 on the form.
There is a list box named List1 on the form.
The Microsoft SQL-DMO Object Library is referenced by the project.

The following list of variables have been declared:

Private oSQLServer As SQLDMO.SQLServer
Private txtServerName As String
Private txtLoginName As String
Private txtPassword As Password

A SQL-DMO connection has been established with the target server. For example:

Private Sub Form_Load()

    On Error Resume Next
    
    txtServerName = "SQLDAG"
    txtLoginName = "sa"
    txtPassword = "password"
   
    Set oSQLServer = New SQLDMO.SQLServer
    oSQLServer.LoginTimeout = 10
    oSQLServer.ODBCPrefix = False

    oSQLServer.ApplicationName = "SQL-DMO Explorer"
    oSQLServer.Connect txtServerName, txtLoginName, txtPassword

End Sub

Here is the code fragment that actually queries the server and iterates through the results, adding each media set name into the list box:

Private Sub Command1_Click()
    Dim oDatabase As SQLDMO.Database
    Dim oTheResults As SQLDMO.QueryResults
    Dim num As Integer
  
    Set oDatabase = oSQLServer.Databases("MSDB", "dbo")
    Set oTheResults = oDatabase.ExecuteWithResults("SELECT name FROM backupmediaset", 50)
     
    'Populate the list
    List1.Clear
    For num = 1 To oTheResults.Rows
        ' Get the only column (MediaSet Names) of the result
        List1.AddItem oTheResults.GetColumnString(num, 1)
    Next
End Sub

Descriptions of the Functions Used in the Code Example

```
Set oDatabase = oSQLServer.Databases("MSDB", "dbo")
```
This statement sets the default database on which you run the Transact-SQL query. The query can access tables from other databases but they must be qualified by the name of the database. For example:

[dbname].[owner].[object name]
```
Set oTheResults = oDatabase.ExecuteWithResults("SELECT name FROM backupmediaset", 50)
```
The ExecuteWithResults function has two parameters:
- A Transact-SQL command string -and-
- Length of the Batch. For example, we are currently using 50 characters.
The Transact-SQL string can be any valid SQL statement. In this case, you have a SELECT statement. However, you can use any Transact-SQL statement. The second parameter, on the other hand, reflects the length of your query string. For more information about this method, see SQL Server Books Online.

The ExecuteWithResults function is provided only to enhance the administrative capabilities of the DMO object model, and is not intended to replace the user-data APIs like ActiveX Data Objects (ADO) and ODBC.
```
List1.AddItem oTheResults.GetColumnString(num, 1)
```
The GetColumnString function has two parameters:
- The row number. -and-
- The column number of the data that you want.
The example iterates through each row (num) of the result set, and adds the first column's value into the list box. For more information about this method, see SQL Server Books Online.