Compare Not Working In VB.NET

lc6529lc6529 Posts: 30
Ok, I am stubborn. I have the project working from the command line using SQLDATACOMPARE /PROJECT:"C:\ProjectName.SDC" but I cannot seem to get this to work in my VB.NET project.

I don't get any errors, I have traced it and it seems happy but my tables are not getting synced. If I launch the same project from the command line all works.

Here is my code...does anyone have any suggestions?
   Sub SyncDatabases(ByVal sServerName As String)
        '
        ' Copied From Synchronizing,Data Example In Help Fule
        '
        Dim objProject As RedGate.SQLDataCompare.Engine.Project = RedGate.SQLDataCompare.Engine.Project.LoadFromDisk("C:\Sync_Labelvision_Master.SDC")

        'get the two databases
        Dim db1 As New Database
        Dim db2 As New Database
        Dim mappings As New SchemaMappings

        'Should check if this is true
        Dim livedb As LiveDatabaseSource = DirectCast(objProject.DataSource1, LiveDatabaseSource)
        db1.RegisterForDataCompare(livedb.ToConnectionProperties())

        'Should check if this is true
        livedb = DirectCast(objProject.DataSource2, LiveDatabaseSource)
        db2.RegisterForDataCompare(livedb.ToConnectionProperties())

        mappings.Options = objProject.Options
        mappings.CreateMappings(db1, db2)

        'Disable any mappings here that you may want....
        Dim oRedGateSession As New ComparisonSession
        oRedGateSession.Options = objProject.Options
        oRedGateSession.CompareDatabases(db1, db2, mappings)

        Dim provider As New SqlProvider
        Dim block As ExecutionBlock

        Try
            block = provider.GetMigrationSQL(oRedGateSession, True)
            Dim stream As IO.FileStream = block.GetFileStream()
            Dim executor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor
            executor.ExecuteBlock(block, sServerName, "[ActualNameOfDB]", True, "[userid]", "[password]")
        Catch ex As OperationCancelledException
            MsgBox(ex.Message)
        Finally
            block = provider.Block
            '
            ' dispose of the objects to delete temporary files  
            '
            If (TypeOf block Is ExecutionBlock) Then block.Dispose()

        End Try
        '
        ' dispose of the objects to delete temporary files
        '
        oRedGateSession.Dispose()
        db1.Dispose()
        db2.Dispose()

    End Sub

Comments

  • After a lot of digging, I came up with this code, however oRedGateSession.TableDifferences.Count always returns 0 even if I delete all records from one of my tables.

    If I run the command line

    SQLDATACOMPARE /SYNC /PROJECT:"C:\Sync_Labelvision_Master.SDC"

    Which uses the SAME project...the tables are synced.

    How can I get the code to work?
     Sub SyncDatabases(ByVal sServerName As String)
            '
            ' Copied From Synchronizing,Data Example In Help File
            '
            Dim objProject As RedGate.SQLDataCompare.Engine.Project = RedGate.SQLDataCompare.Engine.Project.LoadFromDisk("C:\Sync_Labelvision_Master.SDC")
            '
            ' Set Up Our Databases
            '
            Dim dbMaster As New Database
            Dim dbTarget As New Database
            Dim tblMappings As New TableMappings
            Dim oLivedb As LiveDatabaseSource
            '
            ' Register First DB For Comparison
            '
            oLivedb = DirectCast(objProject.DataSource1, LiveDatabaseSource)
            dbMaster.RegisterForDataCompare(oLivedb.ToConnectionProperties())
            '
            ' Register Second DB For Comparison
            '
            oLivedb = DirectCast(objProject.DataSource2, LiveDatabaseSource)
            dbTarget.RegisterForDataCompare(oLivedb.ToConnectionProperties())
    
            tblMappings.Options = objProject.Options
            tblMappings.CreateMappings(dbMaster.Tables, dbTarget.Tables)
    
            Try
                Dim oRedGateSession As New ComparisonSession
                oRedGateSession.Options = objProject.Options
                oRedGateSession.CompareDatabases(dbMaster, dbTarget, tblMappings, SessionSettings.Default)
    
                If oRedGateSession.TableDifferences.Count > 0 Then
                    Dim oProvider As New SqlProvider
                    Dim oExBlock As ExecutionBlock
    
                    oExBlock = oProvider.GetMigrationSQL(oRedGateSession, True)
    
                    Dim stream As IO.FileStream = oExBlock.GetFileStream()
    
                    Dim oBExecutor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor
    
                    oBExecutor.ExecuteBlock(oExBlock, sServerName, "labelvision_master", True, "lvadmin", "lvadmin")
    
                    oExBlock = oProvider.Block
                    If (TypeOf oExBlock Is ExecutionBlock) Then oExBlock.Dispose()
                End If
                oRedGateSession.Dispose()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
             
            End Try
            '
            ' dispose of the objects to delete temporary files
            '
            dbMaster.Dispose()
            dbTarget.Dispose()
    
        End Sub
    
Sign In or Register to comment.