Prev: Sun IDM resources | Multiple Locations
Next: rowversion missing in sql server 2008
From: Joe Cool on 24 Jun 2010 15:03
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?
From: Erland Sommarskog on 24 Jun 2010 16:36
Joe Cool (joecool1969(a)live.com) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Joe Cool on 24 Jun 2010 21:47
On Jun 24, 4:36 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Joe Cool (joecool1...(a)live.com) writes:
> > 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?
>
> 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.

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.
From: Erland Sommarskog on 25 Jun 2010 06:34
Joe Cool (joecool1969(a)live.com) writes:
> I changed the collation to SQL_Latin1_General_CP1_CI_AS with Alter
> Database and all is well.

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Joe Cool on 25 Jun 2010 17:13
On Jun 25, 6:34 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Joe Cool (joecool1...(a)live.com) writes:
> > I changed the collation to SQL_Latin1_General_CP1_CI_AS with Alter
> > Database and all is well.
>
> 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.
>

I was not aware that you could define a database with no collation.
How do I do that?
 |  Next  |  Last
Pages: 1 2
Prev: Sun IDM resources | Multiple Locations
Next: rowversion missing in sql server 2008