Several different errors when using method "GetMigrationSQL"

Bastiaan Molsbeck

I'm using 7.1.0.230 of the assembly "RedGate.SQLDataCompare.Engine.dll".
I'm trying to synchronize two databases: the structure (tables, constraints, etc) is exactly then same, but the contents (database rows) is different.
On most databases, this works perfectly.
But on some databases, this thows errors.
When executing the method "RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL" I get several different errors (depending on the chosen database):

---

FIRST:

---

System.IO.EndOfStreamException: Attempted to read past the end of the stream.
at RedGate.Shared.Utils.IO.LZStream.a(Boolean& )
at RedGate.Shared.Utils.IO.LZStream.Seek(Int64 offset, SeekOrigin origin)
at RedGate.SQLDataCompare.Engine.ResultsStore.Reader.set_Position(Int32 value)
at RedGate.SQLDataCompare.Engine.ResultsStore.Reader.a()
at RedGate.SQLDataCompare.Engine.ResultsStore.Reader..ctor(Store store, aH offsets)
at RedGate.SQLDataCompare.Engine.ResultsStore.Store.GetReader(RowType rowType)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, SelectionDelegate select, Boolean runOnTwo)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, Boolean runOnTwo)

---

SECOND:

---

System.ArgumentOutOfRangeException: Non-negative number required.
Parameter name: count
at System.IO.FileStream.Read(Byte[] array, Int32 offset, Int32 count)
at RedGate.Shared.Utils.IO.LZStream.b(Int32 , Int32 )
at RedGate.Shared.Utils.IO.LZStream.Seek(Int64 offset, SeekOrigin origin)
at RedGate.SQLDataCompare.Engine.ResultsStore.StoreEnumerator.a()
at RedGate.SQLDataCompare.Engine.ResultsStore.StoreEnumerator.MoveNext()
at RedGate.SQLDataCompare.Engine.SqlProvider.b(TableDifference , Reader , RowType , Boolean , ProgressPercentageTracker , SelectionDelegate , Boolean )
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, SelectionDelegate select, Boolean runOnTwo)

---

THIRD:

---

System.IndexOutOfRangeException: Index was outside the bounds of the array.
at RedGate.Shared.Utils.IO.RGLZ.a.a(a )
at RedGate.Shared.Utils.IO.RGLZ.rglz_depack(Byte[] source, Byte[] destination, Int32 depacked_length)
at RedGate.Shared.Utils.IO.LZStream.b(Int32 , Int32 )
at RedGate.Shared.Utils.IO.LZStream.Seek(Int64 offset, SeekOrigin origin)
at RedGate.SQLDataCompare.Engine.ResultsStore.Reader.set_Position(Int32 value)
at RedGate.SQLDataCompare.Engine.ResultsStore.Store.GetReader(RowType rowType)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, SelectionDelegate select, Boolean runOnTwo)

---

Are these known issues in the 7.1 version?
Please advise on how to handle this issue.

slaphead99

I think the first issue may be related to a similar issue that a handful of customer have experienced. Can you try installing the SQL Data Compare 7.2 patch and see if it fixes the 'Index was
outside the bounds of the array.' '' issue?
You can download the patch from here: <ftp://support.red-gate.com/Patches/SQL_Data Compare/SQL Data Compare_7.2.0.4.zip>


Let me know how you get on with these suggestions.

Bastiaan Molsbeck

Hi slaphead99 and thank you for your reply.

I will try the patch, but can you clarify which error the patch should fix?
You say "...the first issue may be related to..." and "...it fixes the 'Index was outside the bounds of the array.' issue".
But the first issue I reported was the "Attempted to read past the end of the stream".
So now I'm confused about which error the patch addresses.

Because those errors occur on different databases (on different environments) I need to make sure which problem was probably fixed by the patch.

Also, the FTP-file you specified does not exist. I get an error in my browser when I paste the URL: "Internet Explorer cannot display the webpage". (Friendly HTTP errors are turned OFF).

Thank you for your answer.

slaphead99

Sorry- I've just a had a word with the product expert who advised a very different course of action.

We'd be happy to try to reproduce your problem if you were willing to send the project over to us (at support@red-gate.com) but this may not even be necessary.

Firstly, this may be a better bet for you as a patch:

ftp://support.red-gate.com/patches/SQL_ ... _patch.zip


Secondly- do you get (similar) problems if you run the supplied SqlProviderExample?

If so, we'll use that as our base for trouble-shooting. If not, obviously the problem will lie in the "diff" of the two projects and it would be good to know in outline, what the differences are.

Bastiaan Molsbeck

Hi slaphead99 and thank you for your reply.

I downloaded the patch to test it on one of the erroneous environments.
But when I try to compile my project with the new assemblies, I get two failures:

1. "Type 'SQLDataCompare.Engine.Project' is not defined."
2. "'ComparisonOptions' is not a member of 'SQLDataCompare.Engine'."

I use the method "LoadFromDisk" of the class "Project" to read the compare project file from disk, because this contains the compare options, and the tables and/or columns to skip, or to determine how to match the rows.

And the "SqlProviderExample" you suggested also does not compile with the dll of the patch.

The version of the new "RedGate.SQLDataCompare.Engine.dll", which is included in the patch, is: 8.0.2.1

Please advise how to handle this.

Chris Auckland

Can you try building the solution that was installed when you installed the patch? It can normally be found in C:\Program Files\Red Gate\SQL Comparison SDK 8\Samples\Automating SQL Data Compare\C#

There have been some changes to the objects in SQL Data Compare version 8, so you will need to make some changes to the v7 code to use the new assemblies.

This solution should compile without making any changes, and will hopefully show you what you will need to change in your existing project to work with the new assemblies.

I hope this helps.

Bastiaan Molsbeck

Hi Chris,

Thank you very much for your reply.
I did manage to build the example solution that was installed with the patch (Automating SQL Data Compare).

With that I was able to fix all (but one!) build errors in my project.
I now use the method "SQLCompare.Engine.Project.LoadFromDisk" to retrieve the project file from disk.

The only build error that I now have is this:
The method "ReplayUserActions" does not exist anymore on the "SQLCompare.Engine.Project" class. It used to be on the "SQLDataCompare.Engine.Project" class.

I found a similar method "ReplayUserActions" on the class "SQLDataCompare.Engine.DataCompareUserActions", but that method has more variables than the previous one.
I cannot find out what to pass as the first parameter of that method. It expects a list of "SQLCompare.Engine.SelectTableEvent", but I cannot find a property of the SQLCompare.Engine.Project class which holds this list.
(I did manage to find out what the other parameters need to be).

Could you please explain how to determine what to pass as the first parameter to this method?
Thank you in advance.

Chris Auckland

I'm afraid the ReplayUserActions method is no longer exposed to the user.

It was never actually intended to be used in previous versions, and as a result, not officially supported or guaranteed to be reliable.

The preferred method to include/exclude specific objects from a comparison is to set the 'include' property for an object.

A simple way to do this would be to use a list, e.g.

//List of tables to exclude
tableList.Add("[dbo].[WidgetPrices]");
tableList.Add("[dbo].[WidgetReferences]");

            // Create the mappings between the two databases
            mappings.TableMappings.CreateMappings(db1.Tables, db2.Tables);

            foreach (TableMapping mapping in mappings.TableMappings)
            {
                if (tableList.Contains(mapping.Obj1.FullyQualifiedName))
                {
                    mapping.Include = false;
                }
                else 
                { 
                    mapping.Include = true;
                }
            }

I hope this is helpful, but I’m sorry if it seems like a loss of functionality.

Bastiaan Molsbeck

Hi Chris and thank you for your reply.

Okay, I understand that I cannot use the method "ReplayUserActions" anymore.

The solution you suggest is pretty comprehensive, because I not only have to include or exclude tables, but my project files also include actions like "DeselectColumn", "SetLeftWhereClause", "SetRightWhereClause", "UseCustomKey" and "SelectColumnAsKey".

Do I really have to examine the project file and loop through all tables, fields, etc. to set the correct action? This seems like a lot of work, and somewhat error prone, actually.
Please consider reincluding a similar function which does this.

Also, if I do try to implement your suggestion, I have three questions:

1. How do I know the exact spelling for the FullyQualifiedName to use? Is it always with brackets [], also for fields? Or do the brackets only have to be used for tables? Is it required that I specify the schema name for tables, event if it is "dbo"?

2. I could not find how to set the action "SetLeftWhereClause" or "SetRightWhereClause" on a table. The class "SQLDataCompare.Engine.TableMapping.Obj1" does not have such property. I see that the class "SQLDataCompare.Engine.TableMapping" has a property "Where", but this applies to both objects (Obj1 and Obj2) I think? How can I specify different "Where" clauses for these two objects?

3. How do I set a custom key for a table, which was specified in the project with the actions "UseCustomKey" and "SelectColumnAsKey"?
Is there a list of all table actions and an example of how to implement them in this new version of the engine dll?

Please advise on these issues.
Kind regards.

Chris Auckland

Yes, you're right; you will need to take a manual approach to make use of the other settings in the project file.

With the current SDK, you can only retrieve the Data sources and project options from the project file. In my opinion, you might as well just set the whole thing up through code.

It might be a bit more work to set up, but it will guard against any corrupt project files, the unreliability of the old replayuseractions method and any future upgrades that change the format of the project file.

With regards to setting up a different where clause for each database, you can use Clause1 for the 1st database, and Clause2 for the second. For example, see the 'WhereExample' in the sample application and change it to be similar to:

mapping.Include=true;
WhereClause c = new WhereClause();
c.Clause1 = "RecordID>=2";
c.Clause2 = "RecordID<=10";
mapping.Where = c;

There is an example of setting up custom table mappings and custom comparison keys in the 'TableMappingExample' of the sample application.

I hope this helps.

Bastiaan Molsbeck

Hi Chris and Slaphead99,

I have some good news!

With the use of your replies and looking at the sample application, I managed to update my code to use the new 8.0.2.1 version of the "RedGate.SQLDataCompare.Engine.dll".
And with that version my application runs smoothly! Hurray!

This means that the previous mentioned bugs are indeed solved in the 8.0 patch, like Slaphead99 suggested.

Thank you very much for your help, both of you!
Kind regards.

Chris Auckland

That's great news, thanks for the update.

Bastiaan Molsbeck

Hi Chris,

I see on the following page that there is a newer version of the SQL Data Compare Engine:
ftp://support.red-gate.com/Patches/SQL_ ... _patch.zip

I am currently using version 8.0.2.1 of the "RedGate.SQLDataCompare.Engine.dll" (in which the previous mentioned problem was solved).
That zipfile contains version 8.1.1.10 of that engine dll.

What do you suggest that I should do? Use the new 8.1.1 version, or stay with the 8.0.2 version?
Is there a list of things that are fixed in the 8.1.1 version in contrast with the 8.0.2 version?

The reason why I ask this, is that want my application to use the version with the least amount of bugs in it. ;-)

Thank you in advance for your reply.
Kind regards.