Unable to RegisterforDataCompare after RegisterForCompare

paquerj

Visual Studio 2005 - VB.Net
Vista Ultimate
SQL 2000 Enterprise

I have a program that gives the users the option to syncronize the schema, synchronize the data, or both.

The schema sync alone works fine and the data sync alone works fine.

But if I call the schema sync and then call the data sync after the schema sync completes, the first database using the RegisterForDataCompare gets registered but it looks like the toolkit goes into an endless loop trying to connect somewhere inside the second call to RegisterForDataCompare.

The status reads "Reading Rules", if that helps at all. Any idea why this would be occuring and what to do about it?

It's the same server, two different databases. If I switch the order of the databases being registered it still hangs registering the second one.

Here's the code:

Dim db3 As New Database
        Dim db4 As New Database

        Dim datablock As ExecutionBlock

        SetTextboxText("Initializing data sync...")

        db3.Status = New StatusEventHandler(AddressOf StatusCallback)

        If SOURCE_TRUSTED_CONNECTION Then
            db3.RegisterForDataCompare(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME), Options.Default)
        Else
            db3.RegisterForDataCompare(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME, SOURCE_USERNAME, SOURCE_PASSWORD), Options.Default)
        End If

        db4.Status = New StatusEventHandler(AddressOf StatusCallback)

        If DESTINATION_TRUSTED_CONNECTION Then
            db4.RegisterForDataCompare(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME), Options.Default)
        Else
            db4.RegisterForDataCompare(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME, DESTINATION_USERNAME, DESTINATION_PASSWORD), Options.Default)
        End If

The dbs used in the schema sync routine are disposed of before the call to the data sync.

Brian Donahue

Hi,

If you are 'recycling' db3 and db4 for use with both Register and RegisterForDataCompare, these objects are probably being destroyed by the Dispose() method, so you would either need to create them anew after disposing them, for example:

Database db3=new Database();
Database db4=new Database();
db3.Register(new ConnectionProperties("server", "db3"));
db4.Register(new ConnectionProperties("server", "db4"));
//Synchronize the schema
db3.Dispose();
db4.Dispose();
// ---> ERROR!!! db3.RegisterForDataCompare(new ConnectionProperties("server", "db3"));
db3=new Database();
db4=new Database();
db3.RegisterForDataCompare(new ConnectionProperties("server", "db3"));
db4.RegisterForDataCompare(new ConnectionProperties("server", "db4"));

Hopefully that's it!

paquerj

Brian -

I don't think that's it. I'm probably doing something stupid but I just don't see it. The project is an upgrade of a VS2003 project that was used to compare against a disconnected database, which worked fine, whereas this one is comparing two live databases. Don't see why that would make a difference, though.

Here's the code, maybe you'll see something obviously wrong:

Private Sub cmdRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRun.Click

        Try

            If SYNCHRONIZE_SCHEMA Then
                SynchSchema()
            End If

            If SYNCHRONIZE_DATA Then
                SynchData()
            End If


        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

   End Sub

   Private Sub SynchSchema()

        Dim db1 As New Database
        Dim db2 As New Database

        Dim block As ExecutionBlock
        Dim backupblock As New ExecutionBlock

        db1.Status = New StatusEventHandler(AddressOf StatusCallback)

        If SOURCE_TRUSTED_CONNECTION Then
            db1.Register(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME), enOptions)
        Else
            db1.Register(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME, SOURCE_USERNAME, SOURCE_PASSWORD), enOptions)
        End If

        db2.Status = New StatusEventHandler(AddressOf StatusCallback)

        If DESTINATION_TRUSTED_CONNECTION Then
            db2.Register(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME), enOptions)
        Else
            db2.Register(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME, DESTINATION_USERNAME, DESTINATION_PASSWORD), enOptions)
        End If

        Dim executor As BlockExecutor = New BlockExecutor
        executor.Status = New StatusEventHandler(AddressOf StatusCallback)

        Dim differences As Differences = db1.CompareWith(db2, enOptions)

        Dim difference As Difference

        For Each difference In differences
            If Not difference.Type = DifferenceType.OnlyIn2 Then
                If difference.Name.IndexOf("dt") = -1 Then
                    If Not difference.DatabaseObjectType = ObjectType.Role And Not difference.DatabaseObjectType = ObjectType.User Then
                        difference.Selected = True
                    Else
                        difference.Selected = False
                    End If
                Else
                    difference.Selected = False
                End If
            Else
                difference.Selected = False
            End If
        Next

        Dim work As Work = New Work

        work.BuildFromDifferences(differences, enOptions, True)

        Dim message As Message

        For Each message In work.Messages
            SetTextboxText(message.Text)
        Next

        SetTextboxText("Warnings:" + vbNewLine + vbNewLine)

        For Each message In work.Warnings '
            SetTextboxText(vbNewLine + message.Text + vbNewLine)
        Next

        SetTextboxText("Running structure sync...")

        block = work.ExecutionBlock

        executor.ExecuteBlock(block, DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME, DESTINATION_TRUSTED_CONNECTION, DESTINATION_USERNAME, DESTINATION_PASSWORD)

        block.Dispose()

        db1.Dispose()
        db2.Dispose()

    End Sub

    Private Sub SynchData()

        Dim db3 As New Database
        Dim db4 As New Database

        Dim datablock As ExecutionBlock

        db3.Status = New StatusEventHandler(AddressOf StatusCallback)

        If SOURCE_TRUSTED_CONNECTION Then
            db3.RegisterForDataCompare(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME), Options.Default)
        Else
            db3.RegisterForDataCompare(New ConnectionProperties(SOURCE_SERVER_NAME, SOURCE_DATABASE_NAME, SOURCE_USERNAME, SOURCE_PASSWORD), Options.Default)
        End If

        db4.Status = New StatusEventHandler(AddressOf StatusCallback)

        If DESTINATION_TRUSTED_CONNECTION Then
            db4.RegisterForDataCompare(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME), Options.Default)
        Else
            db4.RegisterForDataCompare(New ConnectionProperties(DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME, DESTINATION_USERNAME, DESTINATION_PASSWORD), Options.Default)
        End If

        Dim executor As BlockExecutor = New BlockExecutor
        executor.Status = New StatusEventHandler(AddressOf StatusCallback)

        Dim mappings As New SchemaMappings
        mappings.CreateMappings(db3, db4)

        Dim mapping As TableMapping

        Dim tables As String = TABLE_LIST

        For Each mapping In mappings.TableMappings
            If (tables.ToLower.IndexOf(mapping.Obj2.Name.ToLower()) > -1) Then
                mapping.Include = True
            Else
                mapping.Include = False
            End If
        Next

        Dim session As New ComparisonSession
        session.CompareDatabases(db3, db4, mappings)

        Dim provider As New SqlProvider
        datablock = provider.GetMigrationSQL(session, True)

        executor.ExecuteBlock(datablock, DESTINATION_SERVER_NAME, DESTINATION_DATABASE_NAME, DESTINATION_TRUSTED_CONNECTION, DESTINATION_USERNAME, DESTINATION_PASSWORD)

        datablock.Dispose()
        session.Dispose()

        db3.Dispose()
        db4.Dispose()

    End Sub

Thanks,
Robert Paquette

Brian Donahue

The code looks okay. What happens when you do this manually, by synchrionizing with SQL Compare and then Data Compare?

As far as I can see, it must be something about the database that causes this to happen.

paquerj

SQL Compare and then Data Compare run fine. Calling each sub individually runs fine, it only hangs on the db4.RegisterforDataCompare when called in the same sub.

I don't think it's database-related because if I switch the order it will connect fine to db4 and hang on db3. I also put a stop on my servername property and it hits it endlessly so it must be retrying the connection. Don't know why the server would let the first three connect but not the fourth.

Weird. No biggie I guess - I'll just have them run seperately.

Thanks.