SqlOptions.DropConstraintsAndIndexes don't work

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

SqlOptions.DropConstraintsAndIndexes don't work

Postby fluhri » Wed Jun 09, 2010 1:39 pm

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
Code: Select all
    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
fluhri
 
Posts: 2
Joined: Wed Jun 09, 2010 12:51 pm

Postby Brian Donahue » Thu Jun 10, 2010 5:03 pm

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:
Code: Select all
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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby fluhri » Fri Jun 11, 2010 10:18 am

Hi Brian,
thanks, it works.

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

Michael
fluhri
 
Posts: 2
Joined: Wed Jun 09, 2010 12:51 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests