Microsoft KB Archive/241246

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

Article ID: 241246

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q241246



IN THIS TASK
SUMMARY
 * How to List the Results of a SELECT Query by Using SQL-DMO
 * Code Example
 * Descriptions of the Functions Used in the Code Example

REFERENCES



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] </li>  Set oTheResults = oDatabase.ExecuteWithResults("SELECT name FROM backupmediaset", 50) The ExecuteWithResults function has two parameters:

 A Transact-SQL command string -and-

</li> Length of the Batch. For example, we are currently using 50 characters.</li></ul>

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. </li>  List1.AddItem oTheResults.GetColumnString(num, 1) The GetColumnString function has two parameters:

 The row number. -and-

</li> The column number of the data that you want.</li></ul>

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. </li></ul>

back to the top

Keywords: kbhowtomaster KB241246

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.