5

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.

3
  • 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
    Commented Aug 25, 2012 at 10:39
  • As Query is been generated by Application.
    – BJ P
    Commented Aug 25, 2012 at 10:47
  • so application generates the table name in a wrong case?
    – zerkms
    Commented Aug 25, 2012 at 10:52

1 Answer 1

7

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.

8
  • 2
    +1 loved your "rant" on collation conflicts in SQL Server at PASS Summit :-) Absolutely hilarious :-)
    – marc_s
    Commented Aug 25, 2012 at 10:51
  • 2
    That's the one at bit.ly/RFCollation. :)
    – Rob Farley
    Commented Aug 25, 2012 at 12:00
  • in Collation it is showing "Latin1_General_BIN"
    – BJ P
    Commented Aug 25, 2012 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
    Commented Aug 25, 2012 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? Commented Aug 27, 2012 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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