Skip to main content
A
Anonymous
October 29, 2009
Question

Case sensitive issue CF7 & SQL 2005

  • October 29, 2009
  • 4 replies
  • 1065 views

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

    This topic has been closed for replies.

    4 replies

    A
    Anonymous
    October 30, 2009

    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.

    glynjackson
    glynjackson
    Participating Frequently
    October 29, 2009

    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

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    October 29, 2009

    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

    T
    tooMuchTrouble
    Inspiring
    October 29, 2009

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

    D
    Dan_Bracuk
    Inspiring
    October 29, 2009

    where lower(city) = 'rockmart'

    A
    Anonymous
    October 29, 2009

    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.

    D
    Dan_Bracuk
    Inspiring
    October 29, 2009

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices