Index was outside the bounds of the array

FredaFreda Posts: 11
edited July 5, 2006 10:51AM in SQL Compare Previous Versions
When comparing two sql server 2005 databases, I get the following error during the compare phase.
"Index was outside the bounds of the array"

I am able to data compare the two databases and have used other tools to compare them without any problem.

There should not be too many differences between them but we have introduced some unique identifiers.

Can't find any more information about the error. Are they any log files?

Thanks

Comments

  • Hi,

    I'm not sure what might be going wrong here. Are you running version 5 or version 5.1?

    Thanks,

    Tom Harris

    Red Gate Software
  • Hi
    Tried it with both versions - 5 and 5.1
    Thanks
  • Freda wrote:
    Hi
    Tried it with both versions - 5 and 5.1
    Thanks

    Could you tell me using profiler what query is being executed at the time of this error?

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • I have included the last two calls to sql server according to profiler. Both run okay when I run them on the individual databases through management studio.
    Thanks

    SELECT 
    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(VARBINARY(4000), null) as columns
    FROM sys.database_permissions  AS sp WITH (NOLOCK)
    LEFT 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)
    GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, su.name, so.schema_id
    
    UNION ALL
    SELECT
    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, spParent.state,
    so.type AS ObjectType,
    CASE spParent.state WHEN 'R' THEN CONVERT(VARBINARY(4000), null)
    	ELSE PowerSum(sp.minor_id) 
    END AS columns
    FROM sys.database_permissions AS sp WITH (NOLOCK)
    INNER JOIN sys.database_permissions AS spParent 
    	ON spParent.class = 1 AND spParent.major_id=sp.major_id AND spParent.minor_id = 0 AND
    	spParent.grantee_principal_id = sp.grantee_principal_id AND
    	spParent.grantor_principal_id = sp.grantor_principal_id AND
    	spParent.type = sp.type AND
    	spParent.state <> 'R'
    LEFT 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.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO')
    GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type, 
    su.name, so.schema_id
    
    UNION ALL
    SELECT 
    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,
    PowerSum(minor_id) AS columns
    FROM sys.database_permissions AS sp WITH (NOLOCK)
    LEFT 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 class = 1 AND minor_id <> 0 AND state <> 'R'
    GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, 
    su.name, so.schema_id
    
    UNION ALL
    SELECT
    sp.grantee_principal_id AS uid,
    su.name AS UserName,
    CASE sp.class 
    	WHEN 3 THEN (SELECT TOP 1 sch2.name FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
    	WHEN 4 THEN (SELECT TOP 1 sp2.name FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
    	WHEN 5 THEN (SELECT TOP 1 sa2.name FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    	WHEN 6 THEN (SELECT TOP 1 st2.name FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id)
    	WHEN 10 THEN (SELECT TOP 1 sx2.name FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id)
    	WHEN 15 THEN (SELECT TOP 1 smt2.name FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id) COLLATE database_default
    	WHEN 16 THEN (SELECT TOP 1 ssc2.name FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    	WHEN 17 THEN (SELECT TOP 1 ss2.name FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    	WHEN 18 THEN (SELECT TOP 1 srs2.name FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    	WHEN 19 THEN (SELECT TOP 1 sr2.name FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    	WHEN 23 THEN (SELECT TOP 1 sft2.name FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)	
    	WHEN 24 THEN (SELECT TOP 1 ssk2.name FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
    	WHEN 26 THEN (SELECT TOP 1 sak2.name FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
    	WHEN 25 THEN (SELECT TOP 1 sc2.name FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
    	ELSE N''
    END AS ObjectName,
    CASE sp.class 
    	WHEN 10 THEN SCHEMA_NAME(( SELECT TOP 1 sx2.schema_id FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id))
    	WHEN 6 THEN SCHEMA_NAME((SELECT TOP 1 st2.schema_id FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id))
    ELSE 
    USER_NAME(CASE sp.class 
    	WHEN 3 THEN (SELECT TOP 1 sch2.principal_id FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
    	WHEN 4 THEN (SELECT TOP 1 sp2.owning_principal_id FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
    	WHEN 5 THEN (SELECT TOP 1 sa2.principal_id FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    	WHEN 15 THEN (SELECT TOP 1 smt2.principal_id FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
    	WHEN 16 THEN (SELECT TOP 1 ssc2.principal_id FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    	WHEN 17 THEN (SELECT TOP 1 ss2.principal_id FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    	WHEN 18 THEN (SELECT TOP 1 srs2.principal_id FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    	WHEN 19 THEN (SELECT TOP 1 sr2.principal_id FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    	WHEN 23 THEN (SELECT TOP 1 sft2.principal_id FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)
    	WHEN 24 THEN (SELECT TOP 1 ssk2.principal_id FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
    	WHEN 26 THEN (SELECT TOP 1 sak2.principal_id FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
    	WHEN 25 THEN (SELECT TOP 1 sc2.principal_id FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
    	ELSE null
    END)
    END AS ObjectOwner,
    sp.class, sp.type, sp.state,
    CONVERT(CHAR(2), NULL) AS ObjectType,
    CONVERT(VARBINARY(4000), null) AS columns
    FROM sys.database_permissions AS sp WITH (NOLOCK)
    LEFT JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
    WHERE major_id>=0 AND sp.class <> 1
    
    SELECT CONVERT(bit, DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) AS FullTextEnabled
    
    [/code]
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Does this also happen when you use an SA login? I think this could be related to another issue we had last week where we got an error because the user doing the compare didn't have the permissions to view the rights on an object.
  • We did check that and I have just confirmed it to make sure. It comes up with the same error when logging in with sa.
    Thanks
  • Hello,

    I have exactly the same problem. Is this issue resolved?
  • Hello,

    I have exactly the same problem. Is this issue resolved?

    Could you send me the result of the query in an email to Andras.Belokosztolszki at red-gate.com or in a private message?

    Thanks
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
  • The problem was that i have 'clr stored procedures' and assemblies that i deploy rigth to the two servers i want to compare.

    If i delete the CLR stored procedures from the second SQL-server all works fine. It's a bug.

    Now can i compare my development database og my test database so often i need. Wonderful !!
  • Hi Philippe,

    Is it possible for you explain the issue further, for example could you give us a step by step guide to how to create the bug, or is it possible for you to supply us with the assembly that was causing the problem? This way we can get to the bottom of this issue.

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • I am getting this error on only one database in 2k5 when doing a compare.

    what was the resolution to this?
  • I updated to 5.2 and all is fine now... just an FYI
  • Thanks for letting us know...
    Regards
    David
This discussion has been closed.