How can we help you today? How can we help you today?

Compare Not Working In VB.NET

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
lc6529
0

Comments

1 comment

Sort by
  • lc6529
    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
    lc6529
    0

Add comment

Please sign in to leave a comment.