Invalid object name 'dbo.customers'
2007-05May-26
Microsoft SQL server assigns character fields a collate sequence that determines how a field is sorted. These collate sequences can be case-sensitive or case-insensitive which makes it possible to write queries without using UPPER() on every single field name.
A table that is named "Customers" can not be accessed as "customers" in a query. Doing so results in an "Invalid object name" error message. If you work with a single SQL server this behaviour might only be confusing. Unless you know about it, though, it can make working with multiple servers a real hassle.
The database collate sequence is specified at the time you setup the server and a second time when you create the database. Unless you specify a different collate sequence at this point, SQL Server will use the server default value.
Database settings are easy to miss when you compare multiple databases or run your scripts to update the structure. SQL Compare, for instance, reports that two databases are identical when they differ only in the database collate sequence. Most other tools likely do the same as they usually compare database objects.
Even after finding out what the problem is, you are still a step away from a solution. At least on SQL Server 2000 you cannot change this setting using Enterprise Manager. Instead you have to do it in code, for example, in a query window in Query Analyzer. The following statement requires that there's no other connection to the database. If you opened Query Analyzer and Enterprise Manager, you have to close one of them. The statement changes the database to case-insensitive configuration. Be prepared that this statement might take a few minutes to run:
Afterwards it doesn't matter whether you access "Customers" or "customers". Change the collate sequence name as needed.
The database collate sequence is specified at the time you setup the server and a second time when you create the database. Unless you specify a different collate sequence at this point, SQL Server will use the server default value.
Database settings are easy to miss when you compare multiple databases or run your scripts to update the structure. SQL Compare, for instance, reports that two databases are identical when they differ only in the database collate sequence. Most other tools likely do the same as they usually compare database objects.
Even after finding out what the problem is, you are still a step away from a solution. At least on SQL Server 2000 you cannot change this setting using Enterprise Manager. Instead you have to do it in code, for example, in a query window in Query Analyzer. The following statement requires that there's no other connection to the database. If you opened Query Analyzer and Enterprise Manager, you have to close one of them. The statement changes the database to case-insensitive configuration. Be prepared that this statement might take a few minutes to run:
ALTER DATABASE <case sensitive name of database>
COLLATE SQL_Latin1_General_CP1_CI_AS
Afterwards it doesn't matter whether you access "Customers" or "customers". Change the collate sequence name as needed.