The "Duplicate object name has been found" C# Exception

ekhanine · July 11, 2011 6:39PM

The "Duplicate object name has been found" C# Exception is thrown in the Register() method. We have many tables with identical names but owned by different schemas: SchemaA.TableA, SchemaB.TableA, etc.

I'm comparing live databases, the SQL Compare 8 tool has been working fine in all databases for at least a year and it continues to work. All SQL databases are case insensitive.

Seems to me the problem arises from the following code (taken from the exception body below):

RedGate.SQLCompare.Engine.Tables.Add(Table value)

This code does not take into account our database structure.

Any help is appreciated.

Start Exception body

RedGate.SQLCompare.Engine.SqlCompareException was unhandled
Message=A duplicate object name has been found. This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set.
Source=RedGate.SQLCompare.Engine
StackTrace:
at o.a(n )
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
at SQLCompareCodeSnippets.CompareTwoDatabasesExample.RunExample() in C:\Program Files\Red Gate\SQL Comparison SDK 8\Samples\Automating SQL Compare\C#\CompareTwoDatabasesExample.cs:line 50
at SQLCompareCodeSnippets.Program.Main() in C:\Program Files\Red Gate\SQL Comparison SDK 8\Samples\Automating SQL Compare\C#\Program.cs:line 53
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.ArgumentException
Message=An item with the same key has already been added.
Source=mscorlib
StackTrace:
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at RedGate.SQLCompare.Engine.Tables.Add(Table value) at o.j()
at o.a(n )
InnerException:

End Exception body

James B · July 13, 2011 9:04AM

Thanks for your post.

I've just set up something similar; two schemas, "SchemaA" and "SchemaB" - I then created a table ("Table_1") in each.
The sample SDK app (CompareTwoDatabasesExample) still works fine for me.

Can you amend any occurrences of "Options.Default" to be "Options.Default | Options.CaseSensitiveObjectDefinition" and see if it helps?

ekhanine · July 13, 2011 11:43AM

Thank you for your reply. The Options parameter already includes it but the error still comes up:

Start code sample

// Connect to the two databases and read the schema
try
{
Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName);
stagingDB.Register(sourceConnectionProperties, Options.Default | Options.CaseSensitiveObjectDefinition);
}
catch (SqlException e)
{

End code sample

Here is the exception trace again:

Start Exception body

System.ArgumentException was unhandled
Message=An item with the same key has already been added.
Source=mscorlib
StackTrace:
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at RedGate.SQLCompare.Engine.Tables.Add(Table value)
at o.j()
at o.a(n )
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
at SQLCompareCodeSnippets.CompareTwoDatabasesExample.RunExample() in C:\Program Files\Red Gate\SQL Comparison SDK 8\Samples\Automating SQL Compare\C#\CompareTwoDatabasesExample.cs:line 34
at SQLCompareCodeSnippets.Program.Main() in C:\Program Files\Red Gate\SQL Comparison SDK 8\Samples\Automating SQL Compare\C#\Program.cs:line 53
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

What is interesting is that this happens with just one specific database structure. I tested on different databases and only this specific database causes this error. The problem is this is our main database and I have no way to step into your code to get a better idea what is causing this exception.

Love your tools, we use Multi Script (god sent!!), SQL and Data Compare tools, SQL Prompt. Hope to have this issue fixed soon. I'll be out of office for a week so please don't close this thread as I really need to get this working. My next attempt is to try VB.

Thank you

James B · July 13, 2011 11:44AM

Hmm, if it's only happening on one database it's going to be tricky to pinpoint. Does the same thing occur comparing your DB to a new, empty DB? If so, can you send the DB to us so we can see if we can work it out here?

ekhanine · July 13, 2011 11:54AM

I tried various combinations in comparisons: as a source, target, comparing working vs non-working db, comparing between dbs on different servers. No difference.

I've set up a test database where I can drop one table/object at a time and see if I can pinpoint to a specific object causing this. First I'll try VB - maybe VB code handles dictionaries/collections differently although I doubt it.

I didn't find any other post mentioning this exception.

Thank you

James B · July 13, 2011 11:57AM

Yeah - if you can find the object in question it'll be a good starting point.
I think you'll get the same error in VB as it's coming from our own DLL I think. The error occurs when two objects are added that the Compare engine sees as the same thing - it occurs occasionally with SQL Source Control users when they've made a backup copy of some of the files defining their objects in their source control system so it gets read twice, and can't be uniquely identified.

As you've spotted, it's not as simple as Compare not handling the schemas correctly as on other DB's (and my own test) it works.

Out of curiosity, is this DB running on the same version of SQL as the others that *do* work? And is it set to the correct compatibility level?

ekhanine · July 13, 2011 12:04PM

All databases are either on the same SQL Server 2008 instance or on different servers but SQL Server configuration, compatibility and all other options are identical and set to 2008 release.

You are right about different schemas. I tested this hypothesis on a working database, no problem.

Just found out VB is not installed in my VS2010 so that's not going to happen. Off to dropping objects...

ekhanine · July 22, 2011 7:40PM

Findings: I found the dictionary collection where the objects are stored in the SDK sample project as seen in the debugger when the exception is raised (file CompareTwoDatabasesExample.cs, line 23: Database stagingDB.Tables.Raw View.Non-Public members.a[].

This collection contains a list of tables with SQL Server's object_id as Key and SchemaName.TableName as Value. By looking at the run-time values I can peek at all the SQL server tables that were added to the collection.

I found the last table to have successfully added to the collection before the exception is thrown. I dropped and re-created this table in the database, got stuck on another table (total 3 tables that caused exceptions to be raised). After dropping and recreating all three tables the program completed successfully.

When I executed the program on another database - identical schema, different data - a different set of 2 tables caused the same exception. And on yet another I got stuck on three tables. In all cases the tables have different and unique object_id as expected.

I'm lost here frankly. It appears that the collection complains about duplicate keys but the code uses SQL Server object_ids as the key which is by definition unique across the database.

Brian Donahue · July 25, 2011 8:42AM

SQL Compare Engine doesn't use the SQL Server object_id as the key and for reasons you had pointed out, it would make the software unusable for most people if it did that. Duplicate key errors usually happen when the database is case-sensitive and SQL Compare isn't comparing the schema case-sensitively. In your case it could also be that you have mapped the schemata together and then you effectively merge all of the objects into one schema, resulting in duplicate object names.

Brian Donahue · July 25, 2011 11:02AM

I've also noticed identically-named partitioned indexes are acceptable to SQL Server but not to SQL Compare. Perhaps the table had this type of index on it and that's why it worked after dropping/recreating the table?

(bug ID SC-4633)

ekhanine · July 25, 2011 11:27AM

Brian, - the forum format doesn't allow me to post screenshots but I can prove beyond doubt that SQL Compare uses SQL Server object_ids as the keys in this collection. When the exception is thrown I can examine the contents of the a[] collection and all tables (values) and their ids (keys) accumulated before the exception is thrown. The keys are the SQL server object_ids. If you can give me an email I can send the screenshots to I'll send you the screenshots of the collection key-value pairs and SQL server object ids for the tables in the collection.

Brian Donahue · July 25, 2011 11:28AM

Yes it does retain that information but it is not used to index the objects.

ekhanine · July 25, 2011 11:43AM

No, it does use object_ids as the key which is clearly shown in the debugging session when the exception is thrown. Here is an extract from the locals window when the exception is thrown (I re-run the program just a minute ago): the last table to be included in the collection before the exception is thrown:

a[92] key = 2044807338, Value = Table [Forecast].[Exception].

Here is the SQL Server result:

SELECT OBJECT_ID(N'Forecast.Exception'), result: 2044807338

If this is not convincing enough please let me send you a few screenshots. Let's work through this problem assuming the information I provide is correct. Otherwise this will remain a mystery forever.

Thank you

James B · July 25, 2011 12:12PM

I've got a ticket open for you regarding this, so could you mail the screenshot info through to support@red-gate.com with F0049988 in the subject line?

I must admit, I didn't think we used the objectID either, because it would differ in each DB so it wouldn't make sense to compare based on that. In addition, we offer an option to perform a case-sensitive comparison which again would make the objectId irrelevant. Plus of course it should be impossible for you to get duplicates of those anyway as you stated earlier.

If you send over the screenshots I can see if I can tally it up to the relelvant part of the process, but there's definitely something a little odd going on if you can get around the issue by dropping and recreating the objects.

ekhanine · July 25, 2011 6:54PM

The problem has been resolved by updating the DLLs. Once all the DLLs are updated the program works like a charm.

Thank you everybody who helped with this bug.

Great work and very responsive support. Nice!

ghendric · June 18, 2013 10:37AM

Has this bug been resolved in the latest version of SQL Prompt?

The "Duplicate object name has been found" C# Exception

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

The &quot;Duplicate object name has been found&quot; C# Exception

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

The "Duplicate object name has been found" C# Exception