select * from MASTER.DBO.SYSDATABASES
He was showing me two SQL Server 2005 instances, side by side on his screen, and it worked on one but not the other. My initial thought was since the DBO namespace was from SQL Server 2000 and deprecated in SQL Server 2005 and he was running it against a 2005 instance, that perhaps the instance was in the wrong compatibility mode.
After playing with the SQL a bit more, we found the following worked against both instances perfectly:
select * from master.dbo.sysdatabases
Answer: the SQL collation for the instance it was failing on was set to LATVIAN_CP1257_CS_AS. Because the collation selected was case sensitive (as indicated by the "_CS"), the case sensitivity handling applies not just to field contents (such as WHERE and LIKE clauses), but also to table names, including the names of the system tables.
Something to think about if you're writing some SQL for that might be run somewhere other than in the United States...
