INF: How to List the Results of a SELECT Query on a Table using SQL-DMO

• Microsoft SQL Server version 7.0

SUMMARY

To supplement the SQL Books Online topic regarding 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 within 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 applied within a SQL-DMO utility for managing Microsoft Data Engine (MSDE), which does not come with a GUI-based management interface.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200.

For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

The code fragment 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:

  
  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 

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 

• 
  Set oDatabase = oSQLServer.Databases("MSDB", "dbo") 

  This statement sets the default database on which you run the T-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) 

  ExecuteWithResults has two parameters:
  
  
  • T-SQL Command string
  
  
  
  • Length of the Batch (for example, we are currently using 50 characters.)
  
  
  
  The T-SQL string can be any valid SQL statement. In our case, we have a SELECT statement but any T-SQL statement can be used. The second parameter, on the other hand, reflects the length of your query string. For more information on 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 definitely 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. In our example, we iterate through each row (num) of the result set and add the first column's value into the list box. For more information on this method, see SQL Server Books Online.

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo