How to disable case sensitivity in SQL Server 2005 for object name (Table name)?

Question

I have created a new database in SQL Server 2005 and execute generated script from the old database.

In that I have a table called MstCity

When I try to execute the following query which has the table name in uppercase MSTCITY

SELECT City FROM MSTCITY WHERE City = 'Junagadh'

I get an error

Invalid object name 'MSTCITY'

When I execute this SELECT statement it works fine

SELECT City FROM MstCity WHERE City = 'Junagadh'

In the old database both SELECT statement work fine. And both database are in SQL Server 2005 only.

Answer 1

Check the collation on the database. Then consider (but not flippantly) if it should be a CI (Case Insensitive) rather than a CS one.

Also look at the system databases. You may need to do a system rebuild to get it working right if you have built a system to migrate the database to. If your tempdb collation is different from the old box to the new one, you might find yourself with collation errors before too long.