error about unique column name

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

error about unique column name

Postby vincentj » Fri Jan 06, 2012 11:22 pm

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?
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby Anu Deshpande » Mon Jan 09, 2012 11:54 am

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.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby vincentj » Mon Jan 09, 2012 10:57 pm

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?
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby Anu Deshpande » Tue Jan 10, 2012 4:41 pm

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?
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby vincentj » Tue Jan 10, 2012 7:33 pm

Nope, we're not specifying that command line switch. (nor /cs, the shortened alias.)
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby Chris Auckland » Mon Jan 16, 2012 6:38 pm

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby vincentj » Mon Jan 16, 2012 7:11 pm

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.
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby dhtucker » Tue Feb 28, 2012 1:27 am

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
Doug Tucker
Software Engineer / DBA
RenWeb School Management Software
dhtucker
 
Posts: 41
Joined: Fri May 15, 2009 2:46 am
Location: Dallas/FtWorth, Texas


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests