Problem with handling COM events in VB.NET

Question

I'm calling the SQLDMO 8.0 COM library from VB.NET (using a PIA I generated with tlbimp) in order to backup a database with percentage completion notification:

Dim server As SQLDMO.SQLServer = Nothing
Dim backup As SQLDMO.Backup = Nothing
Dim restore As SQLDMO.Restore = Nothing
Dim backupAbortable As Boolean
Dim restoreAbortable As Boolean
Try
    server = New SQLDMO.SQLServer
    server.LoginSecure = True
    server.Connect(serverName)

    backup = New SQLDMO.Backup
    backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
    backup.BackupSetDescription = "test"
    backup.BackupSetName = "test"
    backup.Database = databaseName
    backup.Files = TransactSqlName.Delimit(fileName)
    backup.TruncateLog = SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_Truncate
    backup.PercentCompleteNotification = 1
    AddHandler backup.PercentComplete, AddressOf OnOperationPercentComplete
    AddHandler backup.NextMedia, AddressOf OnOperationNextMedia
    AddHandler backup.Complete, AddressOf OnOperationComplete
    backupAbortable = True
    backup.SQLBackup(server)
    backupAbortable = False

    restore = New SQLDMO.Restore
    restore.Files = backup.Files
    AddHandler restore.PercentComplete, AddressOf OnOperationPercentComplete
    AddHandler restore.NextMedia, AddressOf OnOperationNextMedia
    AddHandler restore.Complete, AddressOf OnOperationComplete
    restoreAbortable = True
    restore.SQLVerify(server)
    restoreAbortable = False

    server.DisConnect()
Catch ex As AbortException
    If backupAbortable Then
        backup.Abort()
    End If
    If restoreAbortable Then
        restore.Abort()
    End If
Finally
    If restore IsNot Nothing Then
        RemoveHandler restore.PercentComplete, AddressOf OnOperationPercentComplete
        RemoveHandler restore.NextMedia, AddressOf OnOperationNextMedia
        RemoveHandler restore.Complete, AddressOf OnOperationComplete
        Marshal.FinalReleaseComObject(restore)
        restore = Nothing
    End If
    If backup IsNot Nothing Then
        RemoveHandler backup.PercentComplete, AddressOf OnOperationPercentComplete
        RemoveHandler backup.NextMedia, AddressOf OnOperationNextMedia
        RemoveHandler backup.Complete, AddressOf OnOperationComplete
        Marshal.FinalReleaseComObject(backup)
        backup = Nothing
    End If
    If server IsNot Nothing Then
        Marshal.FinalReleaseComObject(server)
        server = Nothing
    End If
End Try

This works fine apart from the event handlers - only the first one wired up actually executes. I can't say for sure about the NextMedia event because it only fires for tape backups, but as for the other two, I either get the Complete event or the PercentComplete event firing depending on the order of the AddHandler statements, never both at the same time.

Possibilities:

1. I'm doing it all wrong (suggestions welcome!)
2. There's a bug in SQLDMO 8.0, and it's genuinely only firing one of the events.
3. There's a bug in the RCW or the VB.NET compiler that affects COM interop events.

Any ideas?

Answer 1

This works in VB.NET 2005, not sure about 1.1.

I know you've done a heap of work on your COM interop, but could you just use the Info Events out of your connection

Note the STATS = 10.. that means give progress notifications every 10%

this is just a snippet from a project I've been working on, I hope you can get what you need from it.

public sub Backup()
  Dim Conn As SqlClient.SqlConnection
  dim theCommand as SqlClient.SQLCommand
  Conn = New SqlClient.SqlConnection("Data Source=.\MyInstance;Initial Catalog=Master;Integrated Security=SSPI;")
  theCommand = Conn.CreateCommand

  theCommand.CommandText = "BACKUP DATABASE [MyDatabase] TO DISK = '" & mDatabasePath & "\" & Filenames.SQLBackup & "' WITH NOFORMAT, INIT, NAME = 'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

  AddHandler Conn.InfoMessage, AddressOf onSqlInfoMessage
  'make sure the events are fired as they are available, instead of at the end.
  Conn.FireInfoMessageEventOnUserErrors = True

  Conn.Open()

  theCommand.ExecuteNonQuery()
      RemoveHandler Conn.InfoMessage, AddressOf onSqlInfoMessage

  Conn.Close()

end sub 

   Private Sub onSqlInfoMessage(ByVal sender As Object, ByVal args As SqlClient.SqlInfoMessageEventArgs)
        If args.Message.EndsWith("percent processed.") Then
            Dim theMatch As System.Text.RegularExpressions.Match
            theMatch = mRegEx.Match(args.Message)
            debug.print("Progress = " & theMatch.Value.Trim)
        End If
    End Sub