"Index was outside the bounds of the array." with v5.2.0.32

Detmar

We are getting the "Index was outside the bounds of the array." error in the Comparing step when comparing two SQL Server 2005 databases that contain many assemblies (>30).

This problem has already been reported for a previous version according to this forum. I followed the hint to delete all assemblies in the destination database which allowed me at least to compare the other database objects.

All ideas on how to make the compare process work without deleting the assemblies are highly appreciated.

choppa

I'm also getting this error although I only have 3 assemblies

Brian Donahue

Hi,

Can you please send one of the assemblies (in .dll form) to support@red-gate.com? If upgrading to version 5.2 didn't sort out the problem, then it must be the issue to do with non-EMCA compliant assemblies that may have been produced in beta versions of Visual Studio.

choppa

No beta version of visual studio being used here sorry. This is the released version.

Brian Donahue

Hi,

Okay, but can you please send the assemblies anyway?

Thanks.

Brian Donahue

Hi Mark,

I was able to create the assembly and the asymmetric key in a database and migrate the assembly to another database in SQL Compare's synchronization wizard. So I think we can rule out the assembly format.

I don't have a tremendous amount of experience in this area, but is it possible that there are functions or types bound to this assembly that don't exist in the assembly itself? That could explain the index out of bounds.

Brian Donahue

Could there be assemblies in the database without files? SQL Compare first gets a list of assemblies brom the system tables, then a list of files related to those assemblies. If you run these two queries, do you see any assemblies reported without corresponding files? For each assemblyid in the first query, are there corresponding files in the second query?

SELECT s.name, p.name AS owner, s.assembly_id, s.is_visible, ASSEMBLYPROPERTY(s.name, 'MvID') as MvID,
 s.permission_set, s.clr_name 
FROM sys.assemblies AS s WITH (NOLOCK)
LEFT JOIN sys.database_principals p WITH (NOLOCK) ON p.principal_id=s.principal_id

SELECT assembly_id, name, file_id, content FROM sys.assembly_files ORDER BY assembly_id, file_id

choppa

I've run the queries and both return nothing unfortunately.

Brian Donahue

That would go some way towards explaining the problem. There must be some kind of consistency issue in the system tables.

Is there anything in the sys.assemblies table (select * from sys.assemblies)?

Can you try running DBCC CHECKDB against the database?

choppa

Sorry my mistake.

I ran the queries against the master database, not our own one :roll:

When I run the query against our database I get the following

CSData	1	65677	csdata, version=1.3.4.0, culture=neutral, publickeytoken=c7a5bdc0ab204472, processorarchitecture=msil	3	UNSAFE_ACCESS	1	2006-10-19 15:42:32.607	2006-10-19 15:42:32.607

and

65677	D:\SQLStaging\CSData.dll	1 0x4D5A9000030000000400...

Brian Donahue

Maybe the problem is not specifically assemblies then. Has the database got XML Schema collections, message queues or any other SQL 2005 specific things in it?

choppa

No, nothing like that. The only thing out of the ordinary that we do is use assemblies marked as unsafe. The other database instances we have using the same schema and assembles do not seem to have this problem.

Brian Donahue

I Just noticed something... the fifth column of results should be a GUID (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx), I don't see this in the results of the query. As far as I can see, the software would fail on getting the assembly information if the GUID is not present.

Can you run

ASSEMBLYPROPERTY('CSData', 'MvID')

? Does this return NULL?

choppa

There must have been a problem with pasting the results in my post.

CSData	dbo	65677	1	1CF020E0-DF19-4786-9CE8-D727A59B231E	3	csdata, version=1.3.4.0, culture=neutral, publickeytoken=c7a5bdc0ab204472, processorarchitecture=msil

Brian Donahue

Hello,

Are you using the 'Map Owners' button in the comparison settings? Different logic applies to the database object comparison when you substitute ownership this way.

choppa

No we're not using the map owners functionality at all.

Brian Donahue

I am going to email you a test application. Can you run this against your database and see if it produces any errors?

My guess is that there is a problem with an array of objects that are members of SQL Compare's 'Assembly' class. It could be something like an incorrectly-formatted extended property or the inability to access the permissions for the object because you do not have sufficient access to view the permissions, for instance. Let's try the test application first and see if it reveals the problem; if not we can drill deeper into it.

Brian Donahue

It does look as if the assembly is non-EMCA compliant now that I've got your snapshot and have run SQL Compare under a debugger. There are some mandatory fields that are '0' when they should be '1', so when SQL Compare tries to read them, it starts at the -1 position of the array, hence the 'out-of-bounds' error.

I will see if there is a fix for this.

Brian Donahue

I think that one possibility is that SQL Compare didn't register the assembly properly for some reason. The assembly in the snapshot doesn't pass the EMCA checks, but the dll that you'd sent is perfectly fine and can be deployed on one of my databases without a problem.

Can you try dropping the assembly from the database and re-deploying it again to see if that helps?

Brian Donahue

Thanks Detmar!

Unfortunately when I try to run this in SQL 2005 SP 1, I get

Msg 6544, Level 16, State 1, Line 2
CREATE ASSEMBLY for assembly 'dbo.SampleStrToDate' failed because assembly 'dbo.SampleStrToDate' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

Note I changed the ownership/schema to dbo, but I don't think this makes a difference?

Detmar

Thanks for trying so quickly. Apparently the line has been truncated when I submitted the posting (it was fine in the preview).

I have sent the code to your support mailbox.

Brian Donahue

Hi Detmar,

Still no reproduction on the problem. The assembly slips right into the database after creating schema NBL and user Eureka in two dbs and comparing the two. I have no idea what is going wrong.

Brian Donahue

This problem should be fixed in the version 5.3 release that we'd done on Friday. Thanks for all your help in tracking down the problem!

culpepmw

I am working on a project where the results seem to be very similar. There are several assemblies that are registered in both the source and target dbs. Some are third-party, some are custom in-house code. All are set to run unsafe (file manipulation and de/encryption).

Anyway, now we move on to the interesting part. After reading this and other threads, I tried deleting the unsafe threads from the destination and the compare is SUCCESSFUL!!!

We are in the process of recovering a customer facing server and the inability to certify the completeness of the db has had me a bit "concerned". I guess I have come to rely on the SQL Compare tools as a certification method as well as deployment and troubleshooting.

So, deleting the assemblies from the target db seems to have resolved this issue. We had gone through much trouble of setting up asymmetric keys, logins, and users to be able to run .Net assemblies unsafe without having to resort to "trusted" settings at the db level.

Oh, and I was getting the same results ("index was outside the bounds of the array") regardless of the version of SQL Compare (v5 or v6 latest build in trial mode).

I hope this helps someone somewhere.

darrenweiner

I was very surprised to still get this error with v 6.0 version..actually, it seems to be something I can't work around at all..even if my assemblies are unregistered in one server but registered in another, it's not letting me continue past the Comparing stage.

This seems to be a problem when comparing a 32 bit machine to a 64 bit machine...I can compare 64 instances to each other just fine.

Is there a workaround to this? Is there a way to test the dll components or something?

Thanks