What are the challenges you face when working across database platforms? Take the survey

KeyNotFoundException in BuildFromDifferences

doadoa Posts: 5

I use SDK 8 to generate a change script between two databases.
Database sourceDB = new Database();
Database destinationDB = new Database();
sourceDB.Register(new ConnectionProperties(sServer, sDB, sUser, sPass), Options.Default);
destinationDB.Register(new ConnectionProperties(dServer, dDB, dUser, dPass), Options.Default);

Differences differences = sourceDB.CompareWith(destinationDB, Options.Default);

foreach (Difference difference in differences)
    difference.Selected = true;

Work work = new Work();

work.BuildFromDifferences(differences, Options.Default, true);

I got a KeyNotFoundException from BuildFromDifferences.
"The given key was not present in the dictionary."

The difference collection contains several elements and seems OK

at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at S..ctor(Permissions permissions, Permissions targetPermissions, Options options)
at RedGate.SQLCompare.Engine.Permissions.a(IBlockWriter , Options , Permissions , Boolean , Boolean , String , Boolean& )
at RedGate.SQLCompare.Engine.Work.a(Options , IBlockWriter , U , Boolean )
at RedGate.SQLCompare.Engine.Work.GenerateSql(Differences differences, Difference onlyDifferenceToInclude, Options options, Boolean runOnTwo)
at RedGate.SQLCompare.Engine.Work.BuildFromDifferences(Differences differences, Difference onlyDifferenceToInclude, Options options, Boolean runOnTwo)
at RedGate.SQLCompare.Engine.Work.BuildFromDifferences(Differences differences, Options options, Boolean runOnTwo)
at GenerateDiffScript(String connectionString) in file

FileVersion info:
RedGate.SQLCompare.Engine.dll -

I cannot see that I'm doing something wrong? Is there a bug in SQL Comparison SDK 8?


  • Options
    Can you please run the following query? There are some new SQL Server 2008 permissions that aren't supported by SQL Compare. One of them is the "view change tracking" permission.
    SELECT CAST(0 AS INT) AS ColumnPermissionID, sp.grantee_principal_id as uid, su.name AS UserName, 
    OBJECT_NAME(sp.major_id) AS ObjectName, 
    SCHEMA_NAME(so.schema_id) AS ObjectOwner, 
    1 AS class, sp.type, sp.state, 
    so.type AS ObjectType, 
    CONVERT(int, null) as columns 
    FROM sys.database_permissions AS sp WITH (NOLOCK) INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id WHERE sp.major_id>0 AND sp.class = 1 AND sp.minor_id = 0 AND (sp.type in ('IN','DL','EX') OR CHARINDEX(sp.type, 'CRFNCRTBCRDBCRVWCRPRBADBCRDFBALOCRRU')%4 > 0) AND so.schema_id IS NOT NULL AND sp.type='VWCT' 
    GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, su.name, so.schema_id
    This query will return rows when objects in your database use this right.
  • Options
    The query doesn't return any rows. We are using change tracking on the database and most tables. If I use the SQL Compare UI program it works and I can generate the script I want. I only get this excepion when I use the SDK.
  • Options
    Can you try referencing the DLLs from the SQL Compare (UI) installation folder? Possibly you are encountering a bug that has been fixed in SQL Compare 8.1. Updating the SQLCompare.Engine references should get your program to behave in the same way as SQL Compare.
  • Options

    I'm already refering RedGate.SQLCompare.Engine.dll version

    MSSQL 2008 was released over one year ago. Are you saying that i am the first one trying make a change script on SQL server 2008 with "view change tracking permission" with SDK 8.1?
  • Options
    No, this is not the first time this has come up, which is exactly why I'd asked. SQL Compare 7.2 and up are supposed to support SQL Server 2008, I have just seen that one or two permission types have been left out by accident.
  • Options
    When do you have a new version that will support "view change tracking" permission.
Sign In or Register to comment.