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:

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

Any ideas?

The file version of sqldmo.dll is 2000.85.2312.0, and the typelib version is 8.0. The version of SQL Server that the code is being run against can be either 2000 or 2005. I'd better go and double-check that the same behaviour is happening on both versions. — Christian Hayter
– Christian Hayter, Commented Jun 7, 2009 at 13:35
Sorry, typelib version is 8.5. It seems that SQL 2005 upgraded my original SQL 2000 installation. :-) — Christian Hayter
– Christian Hayter, Commented Jun 7, 2009 at 13:37

Paul Farry · Accepted Answer · 2009-06-18 03:05:45Z

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

answered Jun 18, 2009 at 3:05

Paul Farry

4,7682 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Hayter Over a year ago

Thanks Paul, I didn't know about the FireInfoMessageEventOnUserErrors property. This is a much better solution than using SQLDMO and I have converted all the backup/restore code back to ADO.NET. Nice one. :-)

Collectives™ on Stack Overflow

Problem with handling COM events in VB.NET

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related