Index was outside the bounds of the array

Freda

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

Tom Harris

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

Freda

Hi
Tried it with both versions - 5 and 5.1
Thanks

Andras B

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

Freda

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 Donahue

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.

Freda

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

Philippe Béal

Hello,

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

Andras B

Philippe Béal wrote:

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

Philippe Béal

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 !!

JonathanWatts

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

Cfavero

I am getting this error on only one database in 2k5 when doing a compare.

what was the resolution to this?

Cfavero

I updated to 5.2 and all is fine now... just an FYI

david connell

Thanks for letting us know...
Regards
David