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


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.

  • 1
    Why do you type them in different cases? Does the case you use depend on your mood or is there something more technical behind this question?
    – zerkms
    Aug 25 '12 at 10:39
  • As Query is been generated by Application.
    – BJ P
    Aug 25 '12 at 10:47
  • so application generates the table name in a wrong case?
    – zerkms
    Aug 25 '12 at 10:52

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.

  • 2
    +1 loved your "rant" on collation conflicts in SQL Server at PASS Summit :-) Absolutely hilarious :-)
    – marc_s
    Aug 25 '12 at 10:51
  • 2
    That's the one at bit.ly/RFCollation. :)
    – Rob Farley
    Aug 25 '12 at 12:00
  • in Collation it is showing "Latin1_General_BIN"
    – BJ P
    Aug 25 '12 at 14:31
  • Ok. BIN is the ultimate in sensitivity. Also check sys.columns to see what collation columns are using in the tables you've made.
    – Rob Farley
    Aug 25 '12 at 21:35
  • 1
    No. Table names are stored in their containing database, not in [master]. Database names are stored in [master], maybe that's what you meant? Aug 27 '12 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.