• Article

Handling SQL-DMO Events

This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Some SQL Distributed Management Objects (SQL-DMO)SQL-DMO objects raise events. For example, the Backup object raises events indicating a percent of the operation is complete, that a specified media is full and requires operator action to provide an empty media, and that backup is done. Microsoft Visual Basic implements the keyword WithEvents on object variable dimensioning statements to enable application handling of SQL-DMO events.

WithEvents imposes restrictions on object dimensioning. An object variable allowing event handling must be declared within an object module, such as that associated with a Visual Basic form. Further, WithEvents restricts the use of the keyword New, not allowing its use for shorthand object dimensioning and creation. This Visual Basic statement will return an error:

Private WithEvents oBackup as New SQLDMO.Backup

Object dimensioning must be accomplished in a separate step, as in:

Private WithEvents oBackup as SQLDMO.Backup
Set oBackup = New SQLDMO.Backup

When a SQL-DMO application indicates that it will handle events raised by an instance of a SQL-DMO object, the application must supply subroutines to handle every event raised by the object. You must ensure that executable creation does not inadvertently remove subroutines handling an event.

For example, an application may want to respond to only the PercentComplete event of the Backup object, ignoring the Complete and NextMedia events. You can implement the Complete and NextMedia handlers using a single, processor-inexpensive statement as shown here:

Private Sub oBackup_Complete(ByVal Message As String)
    Exit Sub
End Sub

Private Sub oBackup_NextMedia(ByVal Message As String)
    Exit Sub
End Sub

You can then handle the PercentComplete event, updating a progress bar control on a form as shown below:

Private Sub oBackup_PercentComplete(ByVal Message As String, ByVal Percent As Long)
    frmBackup.ProgressBar.Value = Percent
End Sub

Note

As indicated earlier, Visual Basic allows application response to raised events. To support SQL-DMO event handling, Visual Basic requires that the project reference the SQL-DMO object library. Event handling is not supported when a SQL-DMO object is created using the CreateObject function. Your OLE Automation controller may impose similar restrictions.