KeyNotFoundException in BuildFromDifferences

doa

Hi

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

Stacktrace:
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 - 8.1.0.148

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

Brian Donahue

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.

doa

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.

Brian Donahue

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.

doa

Hi

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

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?

Brian Donahue

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.

doa

When do you have a new version that will support "view change tracking" permission.