I have several databases on a SQL2005 server. All were created the
same way, using the SQL Server Management Studio create new database
wizard. On ONE and ONE ONLY of those databases if I try to generate a
list of tables in that database using:

select table_name from information_schema.tables where table_type =
'BASE TABLE' order by table_name

I get the following error:

Invalid object name 'information_schema.tables'.

If I check the list of system views with the object browser, the view
is indeed there.

This works just fine for all of the other databases on the same
server.

Any ideas why I am getting this error?

Joe Cool (joecool1969 (AT) live (DOT) com) writes:
It's spelled INFORMATION_SCHEMA.TABLES. Which does not matter in a
database with a case-insensitive collation, but in a database
with a case-sensitive or binary collation it does.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 24, 4:36*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
I never considered case sensitivity since I never intenionally make a
database in case sensitive mode. But somehow, the database I was
having problems with had been created with the Latin1_General_BIN,
which apparently is case sensitive. Especially since I changed the
query to reference hte view in uppercase it ran. I'm a little confused
as I didn't make the column references to match case.

I changed the collation to SQL_Latin1_General_CP1_CI_AS with Alter
Database and all is well.

Thanks for the prodding.

Joe Cool (joecool1969 (AT) live (DOT) com) writes:
I recommend against using that collation, or more generally against
SQL collations in general. They are legacy collations, and there can
be some severe performance problems if you have varchar in your tables,
but you have a client that works with nvarchar by default. This combination
has a performance impact with Windows collations, but they are usually
far less severe.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 25, 6:34*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
I was not aware that you could define a database with no collation.
How do I do that?

Joe Cool (joecool1969 (AT) live (DOT) com) writes:
You can't, and I can't say that I said so. What I said is that I recommend
against using SQL collations, and that you are better off using Windows
collation.

You can leave out the COLLATE clause when you create a database, but
then you will inherit the server collation. (Which also should be a
Windows collation.)


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx