Problem with data compare api: ResultsStore is empty

Christian M. Müller

Hi there

I have a problem with the data compare api (version 8.1.1).

My source database is a scripts folder, my target database is a sql server 2008 database.

Here the code:

        Dim dbSourceFaktura As New Database
        Dim dbTargetFaktura As New Database

        Dim sqlProv As New SqlProvider

        dbSourceFaktura.Status = New StatusEventHandler(AddressOf StatusCallbackHandlerSourceFaktura)
        dbTargetFaktura.Status = New StatusEventHandler(AddressOf StatusCallbackHandlerTargetFaktura)

        Dim sdiFaktura As New ScriptDatabaseInformation
        sdiFaktura.SQLServerDBVersion = RedGate.Shared.SQL.Server.SQLVersion.SqlServer2008
        dbSourceFaktura.RegisterForDataCompare(Path.Combine(AppGlobals.Settings.TempWorkPath, "scriptsfolder\faktura"), sdiFaktura, Options.Default)
        dbTargetFaktura.RegisterForDataCompare(New ConnectionProperties(m_Server, m_DbFaktura, m_User, m_Pass), Options.Default)



        Dim mappings As New TableMappings
        mappings.Options = New EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default)

        Dim parameterMapping As TableMapping = mappings.Join(dbSourceFaktura.Tables("[dbo].[Parameter]"), dbTargetFaktura.Tables("[dbo].[Parameter]"))

        parameterMapping.FieldMappings.Clear()
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Key"), parameterMapping.Obj2.Fields("PAR_Key"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_KeyVorgaenger"), parameterMapping.Obj2.Fields("PAR_KeyVorgaenger"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Bezeichnung"), parameterMapping.Obj2.Fields("PAR_Bezeichnung"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Beschreibung"), parameterMapping.Obj2.Fields("PAR_Beschreibung"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Datentyp"), parameterMapping.Obj2.Fields("PAR_Datentyp"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Standardwert"), parameterMapping.Obj2.Fields("PAR_Standardwert"), FieldMappingStatus.Success))
        parameterMapping.FieldMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_KzSystem"), parameterMapping.Obj2.Fields("PAR_KzSystem"), FieldMappingStatus.Success))

        parameterMapping.MatchingMappings.Clear()
        parameterMapping.MatchingMappings.Add(New FieldMapping(parameterMapping.Obj1.Fields("PAR_Key"), parameterMapping.Obj2.Fields("PAR_Key"), FieldMappingStatus.Success))

        parameterMapping.RefreshMappingStatus()

        mappings.Add(parameterMapping)

        Dim sessionFaktura As New RedGate.SQLDataCompare.Engine.ComparisonSession
        sessionFaktura.Status = New StatusEventHandler(AddressOf StatusCallbackHandlerSession)
        sessionFaktura.Options = mappings.Options
        sessionFaktura.CompareDatabases(dbSourceFaktura, dbTargetFaktura, mappings, SessionSettings.Default)


        Dim eb As ExecutionBlock = sqlProv.GetMigrationSQL(sessionFaktura, True)

        Debug.WriteLine(eb.GetString)

        eb.Dispose()

        sessionFaktura.Dispose()

        dbSourceFaktura.Dispose()
        dbTargetFaktura.Dispose()

The Problem is, that the generated sql script does not have any insert or update statements. But i expect about 100 insert statements.
I also tried to use the SchemaMappings object from the examples. With the same result.

The generated sql script looks like this:

/*
Run this script on:

C:\Users\mueller.MITTELSTANDSBUE\AppData\Local\Temp\3987\scriptsfolder\faktura – this database will be modified

to synchronize its data with:

THEODEN.b4y_faktura – this scripts folder will not be modified

You are recommended to back up your database before running this script

Script created by SQL Data Compare version 8.1.1 from Red Gate Software Ltd at 06.10.2010 15:26:11

*/
		
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
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

-- Drop constraints from [dbo].[Parameter]
ALTER TABLE [dbo].[Parameter] DROP CONSTRAINT [FK_Parameter_Parameter]

-- Add constraints to [dbo].[Parameter]
ALTER TABLE [dbo].[Parameter] ADD CONSTRAINT [FK_Parameter_Parameter] FOREIGN KEY ([PAR_KeyVorgaenger]) REFERENCES [dbo].[Parameter] ([PAR_Key])
COMMIT TRANSACTION
GO

And by the way... i guess there is a little bug in the sql script. The source and target databases are wrong.

There must be something wrong im my code.
If i use SQL Data Compare UI (Professional) with exact the same scripts folder and target database to create the sql script its absolutely correct.

I hope somebody can help me!

Thanks

Code Monkey

I've got exactly the same problem here.
I'd need help ASAP

Thx in advance :oops:

Christian M. Müller

I just tried to compare the data between two live databases. Its working.
But why not with the scriptsfolder as the sourcedatabase??

Christian M. Müller

ITS WORKING :lol:

To compare the data between scripts folder and a live db you must set a new FolderDataSource object.
From this object call the LoadScriptDatabaseInfomation-method to retrive a ScriptDatabaseInformation-object.

Then change the SQLServerDBVersion-property to the right sql server version. In my case its RedGate.Shared.SQL.Server.SQLVersion.SqlServer2008.

Now you can pass the ScriptDatabaseInformation-object into the RegisterForDataCompare-method.

 Dim sdiFaktura As New ScriptDatabaseInformation
        Dim ds As New ReadFromFolder.FolderDataSource(Path.Combine(AppGlobals.Settings.TempWorkPath, "scriptsfolder\faktura"))
        sdiFaktura = FolderDataSource.LoadScriptDatabaseInfomation(Path.Combine(AppGlobals.Settings.TempWorkPath, "scriptsfolder\faktura"), ds.Errors)
        sdiFaktura.SQLServerDBVersion = RedGate.Shared.SQL.Server.SQLVersion.SqlServer2008


        dbSourceFaktura.RegisterForDataCompare(Path.Combine(AppGlobals.Settings.TempWorkPath, "scriptsfolder\faktura"), sdiFaktura, Options.Default)
        dbTargetFaktura.RegisterForDataCompare(New ConnectionProperties(m_Server, m_DbFaktura, m_User, m_Pass), Options.Default)