Red Gate forums :: View topic - SqlOptions.DropConstraintsAndIndexes don't work
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

SqlOptions.DropConstraintsAndIndexes don't work

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
fluhri



Joined: 09 Jun 2010
Posts: 2

PostPosted: Wed Jun 09, 2010 1:39 pm    Post subject: SqlOptions.DropConstraintsAndIndexes don't work Reply with quote

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:

    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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu Jun 10, 2010 5:03 pm    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
fluhri



Joined: 09 Jun 2010
Posts: 2

PostPosted: Fri Jun 11, 2010 10:18 am    Post subject: Reply with quote

Hi Brian,
thanks, it works.

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

Michael
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group