Unable to RegisterforDataCompare after RegisterForCompare

paquerjpaquerj Posts: 4
edited May 21, 2007 11:39AM in SQL Toolkit Previous Versions
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.
Robert Paquette

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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!
  • 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
    Robert Paquette
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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.
    Robert Paquette
Sign In or Register to comment.