Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 26 '12 at 20:36

This question came from our site for professional and enthusiast programmers.

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
add comment

1 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.

share|improve this answer
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? –  RBarryYoung Aug 27 '12 at 0:30
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.