version: 1.0.0.0

last updated: 4 January 2003

You would expect regular VB.NET events to work when using SQL-DMO events.

The code that does not work

So you would expect the following VB.NET code to work as expected:

Imports System.Runtime.InteropServices
Imports SQLDMO

Module BackupWithEvents
    <MTAThread()> _
    Sub Main()
        Dim app As App
        app = New App()
        app.Run()
    End Sub
End Module

Public Class App
    Dim server As SQLDMO.SQLServer2Class
    Dim database As SQLDMO.Database2
    Dim backup As SQLDMO.Backup2
    Dim WithEvents backupEvents As SQLDMO.Backup2

    Public Sub Run()
        Try
            server = New SQLDMO.SQLServer2Class()
            backup = New SQLDMO.Backup2Class()
            backupEvents = backup

            server.LoginSecure = True
            server.Connect("(local)\dev")
            Console.WriteLine("server {0}", server.Name)

            database = server.Databases.Item("pubs")
            Console.WriteLine("database {0}", database.Name)

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.SQLBackup(server)

            server.DisConnect()
            backupEvents = Nothing
            backup = Nothing
            server = Nothing

        Catch ex As System.Runtime.InteropServices.COMException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub

    Private Overloads Sub backupEvents_Complete(ByVal Message As String) Handles backupEvents.Complete
        Console.WriteLine(Message)
    End Sub

    Private Overloads Sub backupEvents_NextMedia(ByVal Message As String) Handles backupEvents.NextMedia
        Console.WriteLine(Message)
    End Sub

    Private Overloads Sub backupEvents_PercentComplete(ByVal Message As String, ByVal Percent As Integer) _
        Handles backupEvents.PercentComplete
        Console.WriteLine("{0} {1}", Message, Percent)
    End Sub
End Class

Download BackupWithEvents.zip

The wrong results

However when you look at the results is shows like:
server (local)
database pubs
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.260 seconds (4.541 MB/sec).
 

As you can see there is something wrong, the CompleteEventHandler fires 3 times, the other ones never fires at all. If you would change the order of linking the delegates you will see that the first event handler that you register is the one and only one that fires.

The problem

This behavior is caused by the fact that TLBIMP.EXE, which generates the Runtime Callable Wrappers (RCW) for the SQL-DMO COM library, creates a separate event interface implementation for each delegate rather than having a single interface with multiple functions. It appears that for every event that you want to be advised on, a new “sinkhelper” object, is being created and advised. As a result if one of the events is *dependent* on return value of another event you get unexpected results.

The solution

Technically there are two solutions, the first is to create your own RCW which implements the events handlers correctly using a single interface with multiple functions. This however requires a lot of coding for just handling events. The second solution provides a workaround for the problem using the UCOM interfaces to Advise our own implementation of the sink interface. To properly handle the events, the Visual C# .NET application must implement the IConnectionPointContainer and IConnectionPoint interfaces. The .NET Framework provides managed definitions of these interfaces through the UCOMIConnectionPointContainer and UCOMIConnectionPoint interfaces. These interfaces are part of the System.Runtime.InteropServices namespace.

Using the workaround

When using the UCOM interfaces the code will look like this:

Imports System.Runtime.InteropServices
Imports SQLDMO

Module App
    <MTAThread()> _
    Sub Main()
        Dim server As SQLDMO.SQLServer2Class
        Dim database As SQLDMO.Database2
        Dim backup As SQLDMO.Backup2

        Try
            server = New SQLDMO.SQLServer2Class()
            backup = New SQLDMO.Backup2Class()

            server.LoginSecure = True
            server.Connect("(local)")
            Console.WriteLine("server {0}", server.Name)

            database = server.Databases.Item("pubs")
            Console.WriteLine("database {0}", database.Name)

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"

            'Begin - set up events sink
            Dim cpContainer As UCOMIConnectionPointContainer
            cpContainer = CType(backup, UCOMIConnectionPointContainer)
            Dim cpPoint As UCOMIConnectionPoint
            Dim PES As BackupSink = New BackupSink()
            Dim guid As Guid = New Guid("10021F09-E260-11CF-AE68-00AA004A34D5")
            cpContainer.FindConnectionPoint(guid, cpPoint)
            Dim intCookie As Integer
            cpPoint.Advise(PES, intCookie)
            'End - set up events sink

            backup.Database = database.Name
            backup.Files = "c:\pubs.bak"
            backup.SQLBackup(server)

            cpPoint.Unadvise(intCookie)
            cpPoint = Nothing
            cpContainer = Nothing
            PES = Nothing

            server.DisConnect()
            backup = Nothing
            server = Nothing

        Catch ex As System.Runtime.InteropServices.COMException
            Console.WriteLine(ex.Message)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            Console.ReadLine()
        End Try
    End Sub
End Module

Public Class BackupSink
    Implements SQLDMO.BackupSink

    Public Sub Complete(ByVal Message As String) Implements SQLDMO.BackupSink.Complete
        Console.WriteLine(Message)
    End Sub

    Public Sub NextMedia(ByVal Message As String) Implements SQLDMO.BackupSink.NextMedia
        Console.WriteLine(Message)
    End Sub

    Public Sub PercentComplete(ByVal Message As String, ByVal Percent As Integer) Implements SQLDMO.BackupSink.PercentComplete
        Console.WriteLine("{0} {1}", Message, Percent)
    End Sub
End Class

Download BackupWithEventsFix.zip

The correct results

server (local)
database pubs
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]83 percent backedup. 83
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]99 percent backedup. 99
PercentComplete [Microsoft][ODBC SQL Server Driver][SQL Server]100 percent backed up. 100
Complete [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE successfully processed 145 pages in 0.248 seconds (4.760 MB/sec).

Other SQL-DMO event sink interfaces

If you want to apply the same technique for other SQL-DMO objects that emit events you need to know the GUID of the interface for the event sink, this is the list of objects and the interface UUID for the various event sink interfaces in SQL-DMO

In case you are wondering how to find this information yourself, use OLEVIEW to find the sink interfaces in the type library.

***

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.