Case sensitive issue CF7 & SQL 2005

[Guest]

I had to rebuild a database from a backup recently. Now I am having a case sensitive issue in my queries that I did not have before.

The following use to work with Rockmart in lowercase even though Rockmart was in the table as all uppercase like so ROCKMART.

<cfquery name="updateAreas" datasource="mySource">
   UPDATE Property

   SET AR = 3610
   WHERE  City = 'Rockmart'
</cfquery>

The above has stopped working now the query must specify ROCKMART because it is in the table in all upper case.

My database option is set to SQL_Latin1_General_CP1_CI_AS

Any ideas.

Many thanks for you help.

Brian

[Dan_Bracuk]

where lower(city) = 'rockmart'

[Guest]

Yes I know I can do this but it puzzles me that I wasnt having to do it before. Besides I would have to change all my code.

[Dan_Bracuk]

It might be because you restored to a higher version of Rockport than you had before.  My observations are that database and programming software gets stricter about things like this as they get upgraded.

[tooMuchTrouble]

any chance the City column has it's own collation?

[glynjackson]

By default most SQL Server installations are case "insensitive" which means that SQL Server will not differentiate between upper and lower case characters/letters. So you must have some how change the collation of the server while restoring the database.

Check the server collation property.  It will contain "-CS" or "-CI" to indicate case sensititivity.  To change is a big job maybe easier to just reinstall or change your actual SQL queries for example...

If you want to ensure that you DO get results reguardless of case, you can force it to use a CASE INSENSITIVE collation like so:

SELECT email
FROM sometable
WHERE email = 'cASE@INSENSITIVE-Email.dot' COLLATE SQL_Latin1_General_CP1_CI_AS

[JR__Bob__Dobbs-qSBHQ2]

A collation can be specified at the server, database, and column level in SQL server.  The column collation will take precedence over the database collation.  Check that the collation for the column is case-insensitive.  You can use the query below to get the column's collation.

SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Property'
    AND COLUMN_NAME = 'City'

You can get info on the collations supported by SQL Server at:
http://msdn.microsoft.com/en-us/library/ms144250%28SQL.90%29.aspx

You can change a column's collation if you need to, but you should be sure that this will not affect any other applications which query the same data.  Another option is to specify the desired collation in your query as suggested by glynjackson.
http://msdn.microsoft.com/en-us/library/ms190920%28SQL.90%29.aspx

[Guest]

OK I got the problem resolved finally. I dont know what the quirk was but I created a new database and did a restore into it again. The problem magically went away. Many thanks for everyone helping out. Oh the lessons I have learned.