The given key was not present in the dictionary.

ondrejondrej Posts: 6
edited May 4, 2011 12:38PM in SQL Compare Previous Versions
Just installed SQL Compare and tried to compare two DBs. After clicking "Compare Now", the program got through the "Registering databases", "Creating mappings", and "Mapping database" steps just fine. It, however, then gets stuck on the "Comparing" step (sub-title of the step is "Comparing databases-Comparing databases"). The error message at the bottom of the screen says "The given key was not present in the dictionary.".

This is the first project I am attempting to create with SQL Compare. The same error comes up if I instruct the program to compare one DB to itself.

Ondrej

Comments

  • Eddie DEddie D Posts: 1,800 Rose Gold 5
    Thank you for your post into the forum.

    I am unable to reproduce the reported problem. So a support call has been created for you, the call reference is F0024450.

    If possible, can you please export a SQL Compare snapshot of each data source and send a copies of the snapshot files to support@red-gate.com, quoting your call reference? (File Menu ->Create Snapshot)

    This will help to investigate your problem.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Hi Eddie,

    Any updates on the cause of this problem?

    Thanks,
    Ondrej
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    This has happened one other time in this version of the software, and the cause of the problem was that the VIEW CHANGE TRACKING permission is not yet supported by SQL Compare. If you are using SQL 2008, this may be the problem if you have granted this permission to one of your database users or schemas.
  • Brian,

    I've run a number of variations of "select * from fn_my_permissions(NULL, 'server') order by permission_name asc;" and the VIEW CHANGE TRACKING permission never came up. While I don't think we've explicitly granted this permission to anyone or anything, is there a foolproof way to find out?

    Can I download a previous version of SQL Compare that may not have this issue?

    Thanks,
    Ondrej
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
    
  • Thanks, Brian. 0 rows are returned.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    View Change Tracking must not be the problem then. I take it you have sent snapshot files to Eddie by email?
  • I have. Eddie suspected my account was missing the VIEW SERVER STATE permission, but it wasn't. That's where we are now.
  • I wanted to update thread this in case anybody else gets this error.

    In this particular case, it seems that it was down to one of the database roles being corrupt.

    Dropping and recreating the role fixed the issue.
    Chris
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    We have an update to SQL Compare to fix the "key was not present in the dictionary" error when it is caused by a schema or user that has the "View Change Tracking" permission granted or revoked. If you have this problem, please download the updated installation from here:
    http://www.red-gate.com/messageboard/vi ... php?t=9860

    Please be aware that this is not an "official" release and may have unintended side-effects, so do not install the update unless you specifically have the issue mentioned above!
  • I'm having the same problem now, but with 8.50.25.11. Please advise!
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    This error, like "object reference" errors, is generic and almost always caused by an environment-specific issue with the database. I'll have someone get in touch with you via email with troubleshooting steps.
Sign In or Register to comment.