1

This is a very strange problem. I have a table named exactly "UncSetup". The database is attached to a SQL Server 2012 express instance and accessed in SSMS.

I can execute this query:

SELECT * FROM UNcSEtUp

but not this one:

SELECT *FROM UnCSetup

An "Invalid object name" error is given.

Proof: enter image description here

It's not about the IntelliSense in SSMS. The execution result is wrong.

I have many other databases with exactly the same table schema in which all variants of table names can be executed correctly. So my guess is that this database is somehow corrupted. What can I do to fix this database?

Luo
  • 162
  • 1
  • 12
  • 4
    It's not *corrupted*, it's just using a case sensitive collation. – Damien_The_Unbeliever Dec 12 '18 at 10:42
  • 2
    Check [Case-Sensitivity Setting](https://www.webucator.com/how-to/how-check-case-sensitivity-sql-server.cfm) and to [modify setting](https://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive) – Prashant Pimpale Dec 12 '18 at 10:43
  • @Damien_The_Unbeliever I also thought about it but why can I use "UNcSEtUp"? Are some collations just case sensitive to certain letters? This one's collation is Hungarian_CI_AS. – Luo Dec 12 '18 at 10:52
  • 1
    I believe that in hungarian `Cs` is a separate letter than `C` followed by `S`, correct? So it's not actually case sensitivity that's an issue per se but it's still about the collation – Damien_The_Unbeliever Dec 12 '18 at 10:54
  • 1
    I suspect that is your server's collation, not the databases. What does `SELECT collation_name FROM sys.databases WHERE [name] = N'YourDatabase';` return (obviously replace `YourDatabase` with th ecorrect name). – Thom A Dec 12 '18 at 10:54
  • @Damien_The_Unbeliever I don't know Hungarian... but thank you for the tip. I'll see if I can find something. – Luo Dec 12 '18 at 10:55
  • @Larnu it still returns Hungarian_CI_AS. – Luo Dec 12 '18 at 10:56
  • 1
    Yes, this checks out: compare `SELECT 1 WHERE 'UNcSEtUp' = 'UncSetup' COLLATE Hungarian_CI_AS` and `SELECT 1 WHERE 'UnCSetup' = 'UncSetup' COLLATE Hungarian_CI_AS`. Per Damian, in Hungarian `cS` <> `CS` (`SELECT 1 WHERE 'cS' = 'CS' COLLATE Hungarian_CI_AS` returns nothing). Moral: always use the exact object name and you won't be punished by the collation. – Jeroen Mostert Dec 12 '18 at 10:57
  • What does `SELECT SERVERPROPERTY('collation')` return? – Salman A Dec 12 '18 at 11:02
  • @Salman A it returns 'Finnish_Swedish_CI_AS' – Luo Dec 12 '18 at 11:05
  • @SalmanA: the server collation is irrelevant in this case -- what matters is the collation used in the system catalogs of the database (which was determined when the database was created). The server collation is merely a default used when new databases are created without explicitly specifying the collation -- in most cases, of course, that *will* match the collation of any given database, but when it doesn't looking at it just adds to the confusion. – Jeroen Mostert Dec 12 '18 at 11:12

1 Answers1

0

This question has been answered by @Damien_The_Unbeliever and @Jeroen Mostert in the comments. The database's collation is Hungarian_CI_AS in which 'Cs' is one letter instead of two letters. https://en.wikipedia.org/wiki/Hungarian_alphabet

Luo
  • 162
  • 1
  • 12