SQL Compare 7.1 database/scripts comparison error.

hodgeAG81

Hi,

While comparing a database instance against a set of scripts, I get an error saying that one of the sprocs is duplicated. The specific message is "Unexpected error: A duplicate stored procedure name
([DBO].[usp_ChkLOFCodeExists]) 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."

I searched the folders for a duplicate script, but none was found. I could find no duplicates in the database, visually and with "SELECT NAME FROM sysobjects GROUP BY [NAME] HAVING COUNT( *) > 1".

Comparing this db instance with another db instance (from which the 1st instance was created from) runs ok.

The particular version I am using is 7.1.0.197. When I loaded up SQL Compare 6 (6.20.271), I am able to compare the db against the scripts folders without error.

Is this a bug? We get this whether we are running the compare in the GUI or the command-line. I had our DBA check the database and he says it looks good to him.

Here's the output of running the command-line version:

FAILURE INFORMATION:
Step Name: SQLCompare
Step Status: 1
Step Output:
SQL Compare Command Line V7.1.0.197
==============================================================================
Copyright c Red Gate Software Ltd 1999-2008

Serial Number: XXX-XXX-XXXXXX-XXXX

Registering databases
Creating mappings
Comparing
Unexpected error: A duplicate stored procedure name
([DBO].[usp_ChkLOFCodeExists]) 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.
A duplicate stored procedure name ([DBO].[usp_ChkLOFCodeExists]) 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.
RedGate.SQLCompare.Engine.SqlCompareException
at RedGate.SQLCompare.CommandLine.CommandProcessor.d()
at RedGate.SQLCompare.CommandLine.CommandProcessor.e()
at RedGate.SQLCompare.CommandLine.CommandProcessor.Run()
at RedGate.SQLCompare.CommandLine.Startup.a(String[] ) Caused by:
An item with the same key has already been added.
System.ArgumentException
at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at RedGate.SQLCompare.Engine.Differences.Add(Difference value)

Process completed with exit code 126

Thanks for any assistance.

hodgeAG81

Ok, I rooted around the Red Gate site and came across this:

A duplicate object name has been found

SQL Compare displays this message when you compare a database on a SQL Server that uses case-sensitive sort order and you have not selected the Treat items as case sensitive project option; you must select this project option.

SQL Compare also displays this message if you create a snapshot of a database on a SQL Server that uses case-sensitive sort order and you have not selected the Treat items as case sensitive check box on the Create Database Snapshot dialog box.

So I talked to our DBA again and asked about the case-sensitivity of the sort order. This database has its collation order set to SQL_Latin1_General_CP1_CI_AS. The DBA tells me that the CI means case-insensitive.

But, once I checked Treat items as case sensitive project option and re-ran the compare, I did not get an error.

So basically, I have to tell the compare to compare the db as if it is case sensitive when it is not. Could this have something to do with the scripts and not the database?

Thanks.

hodgeAG81

One of my coworkers is running version 7.0.0.835 and he IS able to do the same compare that I was trying to do without having to set the case sensitive option in the compare project. So there must be something in the 7.1.0.197 release that is causing this issue. I had just upgraded to 7.1 this week.

Thanks.

hodgeAG81

Latest update. I uninstalled SQL Compare 7.1.0.197 and reinstalled SQL Compare 7.0.0.835 and it works fine for me now without having the case sensitive option set.

Brian Donahue

Thanks for your patience. I haven't got a definite answer from the developers on what could have changed in v7.1 to cause this difference.

It may be better to go the other way, and find out where these stored procedures are, and what they look like. I think it may be a common misconception that each SQL object must be in its' own file. In fact you could have many objects in one file, and we have seen this cause this problem before.

What I'd done in the previous case was to find all occurrances of the text in all files using DOS:

cd MyScriptsFolder
for /R %N in (*.sql) do FIND /I "usp_CheckLOFCodeExists" "%N" >> results.txt

Results.txt will contain all of the output from the individual commands and show you all cases where the stored procedure occurs. Once we work that out, we can probably work out why the behavior is different bewtween the two versions. But in order to even get this error, the assumption is that a duplicate object name must exist somewhere in the first place.

Please let us know what you find out.

hodgeAG81

Thanks Brian. I have sent you the results file off-line.

Thanks.

Brian Donahue

There aren't any duplicate stored procedures in your scripts. This does appear to be a bug in SQL Compare that has to do with the case-sensitivity not being applied properly. Although the issue is not specific to script files, it has been fixed in a private build of SQL Compare 7.2 that you can get by contacting support.

Thanks again for your patience!