error about unique column name

vincentj

Hi,

I just updated to SQL Compare 10 on our build server, and now when I sync the database I get the following error:

Column names in each table must be unique. Column name 'foo' in table 'dbo.bar' is specified more than once.

I changed the build script to use version 9 instead and then it worked fine. Do I need to specify some new parameter to make version 10 work the same way as 9?

Anu D

Many thanks for your post and apologies for inconvenience caused.

Can you make sure whether this option 'Use Case sensitive object definition' in Project options --> Behavior is selected or not?

About this option: For databases with case-sensitive collation, enables objects with case-sensitive names to be compared and synchronized. For example, considers object names such as ATable and atable as different and performs case-sensitive comparisons on stored procedures, and so on.

You should use this option only if you have databases with binary or case-sensitive sort order.

Note that you should take care when you change this option. For example, if you create a database snapshot with this option selected and you then compare the snapshot with another database with the option cleared, SQL Compare may produce unexpected errors.

vincentj

You're right, this does look like a case sensitivity issue. The script that SQL Compare generates is trying to add the new column in all lower case and then drop the existing column which is all uppercase. We're using a case-insensitive collation though, so this doesn't make any sense, and it throws an error.

Just for kicks, I tried adding CaseSensitiveObjectDefinition to the command line options (we don't use project files) and I still get the error. It seems like SQL Compare is enabling that option by default. Is there a way to explicitly tell it to do a Case Insensitive comparison?

Anu D

Comparisons are not case-sensitive by default; to specify case sensitive comparisons you would use the /Options switch:

/Options:CaseSensitiveObjectDefinition

Are you specifying it in similar manner?

vincentj

Nope, we're not specifying that command line switch. (nor /cs, the shortened alias.)

Chris Auckland

I'm not exactly sure why you would get an error on a CS database, as it should be fine to add another column with only a case difference to the same table.

Could the source db be CS and the target CI? This still wouldn't explain why it's trying to create a new column before it drops the old column.

I'm going to follow this up with a support ticket and see if we can get to the bottom of it.

vincentj

We've never used a CS database, not sure why it would suddenly be an issue. The source is just a folder of scripts that we check out from Subversion. Also, it's weird that it works fine in version 9 but not in version 10.

dhtucker

I'm experiencing the same problem, and I confirmed that both the source and target databases are of collation: SQL_Latin1_General_CP1_CI_AS