SqlOptions.DropConstraintsAndIndexes don't work

fluhri

Hello,
i compare 2 tables with SQL Compare Data and SQL Compare SDK, but results are different

SQL Compare Data:
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
BEGIN TRANSACTION

-- Drop unused indexes from [dbo].[MenuObjects]
DROP INDEX [Hierarchy] ON [dbo].[MenuObjects]

-- Update rows in [dbo].[MenuObjects]
UPDATE [dbo].[MenuObjects] SET [Hierarchy]='01.03.02.03' WHERE [MenuId]=42
UPDATE [dbo].[MenuObjects] SET [Hierarchy]='01.03.02.04' WHERE [MenuId]=186

-- Add indexes to [dbo].[MenuObjects]
CREATE UNIQUE NONCLUSTERED INDEX [Hierarchy] ON [dbo].[MenuObjects] ([Hierarchy]) ON [PRIMARY]
COMMIT TRANSACTION
GO

SQL Compare SDK:
SET XACT_ABORT ON
GO
SET ARITHABORT ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
BEGIN TRANSACTION

-- Update rows in [dbo].[MenuObjects]
UPDATE [dbo].[MenuObjects] SET [Hierarchy]='01.03.02.03' WHERE [MenuId]=42
UPDATE [dbo].[MenuObjects] SET [Hierarchy]='01.03.02.04' WHERE [MenuId]=186
COMMIT TRANSACTION
GO


The data in table at database TestVD3 are
MenuId Hierarchy
42 01.03.02.02
186 01.03.02.03

data in table at database UPD2
MenuId Hierarchy
42 01.03.02.03
186 01.03.02.04

MenuID is primarykey and Hierachy is unique index.

at last my code for SQL Compare SDK

    Public Sub DBUpdateMenuObjects()
        Dim TestDatabase As New RedGate.SQLCompare.Engine.Database
        Dim CompareDatabase As New RedGate.SQLCompare.Engine.Database
        TestDatabase.RegisterForDataCompare(New RedGate.SQLCompare.Engine.ConnectionProperties("Augenblix\SS8", "UPD2", cUser, cPwd))
        CompareDatabase.RegisterForDataCompare(New RedGate.SQLCompare.Engine.ConnectionProperties("Augenblix\SS8", "TestVD3", cUser, cPwd))

        Dim bReturn As Boolean = False
        Dim TableName As String = "[dbo].[MenuObjects]"

        Dim oSession = New RedGate.SQLDataCompare.Engine.ComparisonSession

        Dim oMappings As New RedGate.SQLDataCompare.Engine.TableMappings
        Dim oTableMapping As RedGate.SQLDataCompare.Engine.TableMapping = CType(oMappings.Join(TestDatabase.Tables(TableName), CompareDatabase.Tables(TableName)), RedGate.SQLDataCompare.Engine.TableMapping)
        If Not (oTableMapping.Status = RedGate.SQLDataCompare.Engine.TableMappingStatus.UnableToCompare) Then
            If Not (oMappings(0) Is Nothing) Then
                If Not (CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).IndexMappings("Hierarchy") Is Nothing) Then
                    CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).IndexMappings.Remove(CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).IndexMappings("Hierarchy"))
                End If
                If Not (CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).MatchingMappings("Hierarchy") Is Nothing) Then
                    CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).MatchingMappings.Remove(CType(oMappings(0), RedGate.SQLDataCompare.Engine.TableMapping).MatchingMappings("Hierarchy"))
                End If
            End If
            oSession.Options.SqlOptions = oSession.Options.SqlOptions Or RedGate.SQLCompare.Engine.SqlOptions.DropConstraintsAndIndexes

            oSession.CompareDatabases(TestDatabase, CompareDatabase, oMappings)
            bReturn = True

            Dim oDifference As RedGate.SQLDataCompare.Engine.TableDifference = oSession.TableDifferences(TableName)
            If (oDifference Is Nothing) And (oSession.TableDifferences.Count > 0) Then oDifference = oSession.TableDifferences(0)

            Dim oProvider As New RedGate.SQLDataCompare.Engine.SqlProvider
            Dim oBlock As RedGate.Shared.SQL.ExecutionBlock.ExecutionBlock
            Try
                oProvider.Options.SqlOptions = oProvider.Options.SqlOptions Or RedGate.SQLCompare.Engine.SqlOptions.DropConstraintsAndIndexes
                oBlock = oProvider.GetMigrationSQL(oSession, oDifference, True)
                System.Diagnostics.Debug.Print(oDifference.ResultsStore.Count.ToString)

                oBlock.SaveToFile("C:\menueobjects.sql", RedGate.Shared.Utils.IO.EncodingType.Unicode)

            Catch ex As Exception
                Throw New Exception(ex.Message)

            Finally
                oSession.Dispose()
                oSession = Nothing
            End Try
        End If
    End Sub

best regards
Michael

Brian Donahue

Hi Michael,

I think the difference is that SQL Data Compare synchronizes all differences, and your code selects just the one (the oDifference object). If you omit this and use another overload, you get the index drop and recreate:

oBlock = oProvider.GetMigrationSQL(oSession, True)

You can limit the selected tables using the mappings, so you don't really need to synchronize individual differences anyway.

I hope this helps.

fluhri

Hi Brian,
thanks, it works.

I need synchronize separately for detailed error and continue after error.
I will compare tables separately.

Michael