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

DetmarDetmar Posts: 3
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.

Comments

  • I'm also getting this error although I only have 3 assemblies :(
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    Can you please send one of the assemblies (in .dll form) to [email protected]? 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.
  • No beta version of visual studio being used here sorry. This is the released version.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    Okay, but can you please send the assemblies anyway?

    Thanks.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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
    
  • I've run the queries and both return nothing unfortunately.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • No we're not using the map owners functionality at all.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    edited November 9, 2006 6:08AM
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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!
  • 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.
  • 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
Sign In or Register to comment.