Post reply

Column Names Case Sensitive

  • March 17, 2007 at 8:17 am

    #177252

    I have recently moved a sql2000 db to 2005.

    I have problem whereby my sql statements now throw errors because it cannot find certain column names in the tsql.

    I have found that the column names in my tsql statements must match their exact case as defined in the column name in the tables - where they werent before in 2000????

    I have literally hundreds of sql statements in many applications - which will need altering.

    What is the reason for this change - and is there a setting to stop this?

     

    Many thanks in advance....

     

  • March 17, 2007 at 8:51 am

    #695576

    Check if the server and database collation settings are case sensitive.  If they are you can change them back to case insensitive to completely solve the problem.

  • March 17, 2007 at 8:57 am

    #695577

    Thanks for that...

    Where do i find/change the collation setting for a database - cant find it anywhere in 2005?

     

    Many thanks again...

     

  • March 17, 2007 at 9:49 am

    #695579

    Not sure.  I'd check the database properties or the books online alter database.  But I don't have any 2005 installed here so I can't check for you.

  • March 17, 2007 at 6:44 pm

    #695592

    select

    name, collation_name from master.sys.databases

    select

    object_name(id) tablename, collation_name from sys.columns

     

    MohammedU
    Microsoft SQL Server MVP

  • March 18, 2007 at 4:10 am

    #695601

    Thanks for that....

    Do you think if i detach then reattach this particular database the collation will then be set as per the master?

    As there are other databases in this instance of sql 2005 that dont have this problem?

    The database in question was a backed up from another server with sql 2005 on it?

     

  • March 18, 2007 at 5:28 am

    #695602

    What results did those 2 queries sent out?

  • March 18, 2007 at 6:48 am

    #695606

    For the databases that i dont have this problem with are set to: Latin1_General_CI_AS

    The one with the problem - its collation is NULL.

    I went into the properties of the database in question and reset the collation as per the the other ones - and it now seems to work fine.

    Thanks very much for your help....

  • March 18, 2007 at 7:23 am

    #695607

    Glad to hear that.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply